IF statement

[Arun]

Is there a way to simply IF statements that, for example, want to change the
result if a zero comes up. I have a formula with a long denominator. I
don't want the DIV0 error so I just want to say "IF the (denominator formula)
is 0, then make it 1. However, I don't want to type that whole formula that
is full of lookups and offsets in twice to accomplish this (once to ask if
it's zero, then again to set the value if it is not zero). If not, I think
I'm just going to write it myself.

CONDITIONALIF ([formula] , [value to check against] , [value if condition is
met])

 

[Dave Peterson]

You could just check the denominator.

=if(denom=0,"",numer/denom)

or if you're using xl2007, you can use =iferror().

Is there a way to simply IF statements that, for example, want to change the
result if a zero comes up. I have a formula with a long denominator. I
don't want the DIV0 error so I just want to say "IF the (denominator formula)
is 0, then make it 1. However, I don't want to type that whole formula that
is full of lookups and offsets in twice to accomplish this (once to ask if
it's zero, then again to set the value if it is not zero). If not, I think
I'm just going to write it myself.

CONDITIONALIF ([formula] , [value to check against] , [value if condition is
met])

 

[Conan Kelly]

Arun,

Sure:

=IF("Denominator formula"=0,1,"Numerator formula"/"Denominator Formula")
(be sure to remove the double quotes)

Just copy & paste the denominator formula so you don't have to retype it.

HTH,

Conan

 

[Michael M]

Hi Arun

Try:
=IF(denominator formula=0,1,denominator formula)
Rather than type it out twice, simply highlight the formula and copy and
paste to the second location. This is a better idea as it also eliminates
typos.

HTH
Michael M

 

[Gord Dibben]

Post an example of one of the current formulas that needs the trap.

Could be several ways to go.

Maybe even a macro to change them all at once with the trap added.


Gord Dibben MS Excel MVP

 

[Arun]

Thank you for all the replies, but it is not an issue of the length of the
formula but rather the run time. The denominator of formula has multiple
index, offset and lookup functions and searches a database of over 5000
entries. The formula is in over 100 different cells. The file already takes
a a long time to calculate, but since it has to run the formula twice each
time (once to check it is not 0 then again to get the value if it is not 0) I
figured I could cut the run time in half by simplifying.

Come to think of it, Dave Peterson's second suggestion using the iferror
function will probably do it.

Thanks again.
Arun

Post an example of one of the current formulas that needs the trap.

Could be several ways to go.

Maybe even a macro to change them all at once with the trap added.


Gord Dibben MS Excel MVP

Is there a way to simply IF statements that, for example, want to change the
result if a zero comes up. I have a formula with a long denominator. I
don't want the DIV0 error so I just want to say "IF the (denominator formula)
is 0, then make it 1. However, I don't want to type that whole formula that
is full of lookups and offsets in twice to accomplish this (once to ask if
it's zero, then again to set the value if it is not zero). If not, I think
I'm just going to write it myself.

CONDITIONALIF ([formula] , [value to check against] , [value if condition is
met])