# Clean up formulas

#### sjgross

##### New Member
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?

-Susan

#### repairman615

##### Well-known Member
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.

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

#### sjgross

##### New Member
I may be mistaken. Is this the whole formula?

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

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

Morning,

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
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
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
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.

1,081,560
Messages
5,359,608
Members
400,538
Latest member
leon_oscar

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...