onerror or catch exception/error on Excel?

[Defranco]

Hi,

For years, when I have a calculation that could result in error I do
something like:

=if(iserr(A1/B1),C1, A1/B1)

So in case of A1/B1 result in some error, it will show C1 - it is a
nice solution for formula error management but there is a little
problem: I need to paste twice the formula (in this case "A1/B1" )...
one for testing and another for displaying the result - this is not
nice for long formulas.

Is possible to have some kind of "onerror()" or try() in Excel for
example it would be something like this:

=onerror(A1/B1, C1)

it would be very simple: in case of not generating an error it would
display A1/B1 result directly, but in case of error it would result
C1.

I'm asking about a single cell formula and A1/B1 is just an example (I
Know that I could test if B1=0 to avoid error, but this is just a
small example).

Sorry if this idea sounds stupids... I just want to know if I'm doing
the correct way for the last years (pasting the same formula twice
inside a single cell).

kind regards

defranco

 

[Bernie Deitrick]

defranco,

Excel 2007 now has the IFERROR function, which does what you want, and would be used like

=IFERROR(A1/B1,C1)

where C1 is returned only if A1/B1 returns an error.

You could write your own IFERROR in VBA, but better to just stay with the way that you are doing it
now.

HTH,
Bernie
MS Excel MVP

 

[Defranco]

Thanks Bernie!