onerror or catch exception/error on Excel?

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
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top