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

"Gord Dibben" wrote:

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

On Thu, 13 Dec 2007 13:44:06 -0800, Arun wrote:

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])