Is(error) or iferror

[LisaM]

I am quite the noob with excel and would like to corrrect the errors from displaying in my worksheet. I have tried looking it up but can't find a simple answer to my question.

I am using Excel 2003 and would like to remove the #VALUE & #NUM! errors from the cells on the worksheet. I'd like instead of those errors to display, I'd like the cell to be blank or to show "-" (without the quotes). I don't care if they show a big fat zero - I just do not want to see the errors. It makes my worksheet cluttered and is distracting.

I am using the following formulas:

=B3*C3

Displays the value correctly if data is entered into those two cells but if no data is entered, #VALUE displays. How do I remove this?

The second formula I am using is:

=INDEX($G$1:$K$1,MATCH(SMALL(G3:J3,COUNTIF(G3:J3,0)+1),G3:J3,0))

Displays #NUM! when no data is entered into the cells but correctly displays what it should when data is entered.

How do I remove these two errors?

 

[Matty]

Hi,

Simplest way:

=IF(ISERROR(yourformula),"what you want to show instead of error",yourformula)

Another way (if values to be returned are numeric):

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},"what you want to show instead of error",yourformula))

If you want to do calculations with the resulting data, consider using a 0 above anything else. If you use the latter formula, the 0 should not be in quotes.

Matty

 

[LisaM]

Thanks a ton! I got the first one working.

 

[Matty]

Thanks a ton! I got the first one working.

Good stuff. Happy I was of help.

Matty