Clean up formulas

sjgross

New Member
Joined
Jul 11, 2002
Messages
12
Good morning,

I have an Excel 2003 workbook with some custom formulas. These formulas all begin with =GetPerData.

A user put conditions on the beginnings of some of the formulas like this:
=IF(V$12=1,GetPerData

The user also used rounding on these formulas, like this:
,ROUND(+V31*V28,0))

Is there a way to remove these extra formula conditions so that the formula just begins with
=GetPerData and has no rounding on the end?

Thanks for any help/advice!

-Susan
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

repairman615

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
I may be mistaken. Is this the whole formula?

Code:
=IF(V$12=1,GetPerData,ROUND(+V31*V28,0))

If so it looks as the first "IF" function is testing for V12=1, if so then GetPerData, else ROUND V31*V28, to the nearest whole number.

So it looks as the GetPerData is only being used when V12=1, the Rounding part has nothing to do with GetPerData as when V12 does not equal 1, the Rounding function takes V31*V28 rounded to the ones place.

Pobably do not need the plus sign in front of v31.

So what happens when you enter
Code:
=GetPerData
Losing the if and round functions?
 

sjgross

New Member
Joined
Jul 11, 2002
Messages
12
I may be mistaken. Is this the whole formula?

Code:
=IF(V$12=1,GetPerData,ROUND(+V31*V28,0))

Pobably do not need the plus sign in front of v31.

So what happens when you enter
Code:
=GetPerData
Losing the if and round functions?

--------

Morning,

Thanks for your reply.

I should clarify, the formula is actually:

=IF(V$12=1,GetPerData,($A1, $B1, $C1),ROUND(+V31*V28,0))

The cell references for the If statement and the ROUND function are all different.

What I need to end up with is:

=GetPerData($A1, $B1, $C1)

I hope that makes a bit more sense.

-Susan
 

repairman615

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
I do not know what this User Defined Function is...but from the looks of it that should work below.

Code:
=GetPerData($A1, $B1, $C1)

What happens when you enter this by itself?

edit: Also you may want to add your code for these udf's to be able to better understand what the GetPerData is actually doing.

Might have an extra comma between GetPerData and the ($A1, $B1, $C1),
comma removed below.
Code:
=IF(V$12=1,GetPerData($A1, $B1, $C1),ROUND(+V31*V28,0))
 
Last edited:

sjgross

New Member
Joined
Jul 11, 2002
Messages
12
I do not know what this User Defined Function is...but from the looks of it that should work below.

Code:
=GetPerData($A1, $B1, $C1)

What happens when you enter this by itself?

The function returns a number.

The problem is that I need to strip off the beginning If statement and the Rounding from this formula. I have a large spreadsheet full of formulas that contain the if statements before my GetPerData function and the rounding after it. In each case the cell references change.

The GetPerData function does work. I just need to have it in the spreadsheet without the if statements and the rounding.

Thanks,

Susan
 

repairman615

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
I see, you might try the find and replace feature.
That may work for you.

I can not think any other easy way to change all of your formulas in the sheet.

I hope that find and replace works for you.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,380
Messages
5,601,291
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top