onerror or catch exception/error on Excel?

  • Thread starter Thread starter Defranco
  • Start date Start date
D

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
 
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
 
Back
Top