asked on # Simplest way to change what is displayed when an Excel formula results in an error

Hello,

I seem to remember a simple and shorter way to define results when adding the Excel ISERROR() function to the beginning of a formula.

Can someone tell me if that's true, and if so, remind me what it is?

At the time this issue came to mind, I was using the following formula in a setting in which I knew that many of the cells would have errors (ie the presence of the errors is not the problem):

*my desire is simply to display a more subtle indicator for error*, namely, a backtick (`) (instead of the #N/A).

I came up with the following solution, and it does the trick (ie displays ` rather than #N/A in cells with errors) but its a bulkier and more cumbersome formula than what I remember:

Thanks

I seem to remember a simple and shorter way to define results when adding the Excel ISERROR() function to the beginning of a formula.

Can someone tell me if that's true, and if so, remind me what it is?

At the time this issue came to mind, I was using the following formula in a setting in which I knew that many of the cells would have errors (ie the presence of the errors is not the problem):

```
=VLOOKUP(VALUE(TRIM(SUBSTITUTE(S17,"G",""))),Table,2,0)
```

However, the error cells currently display #N/A and I came up with the following solution, and it does the trick (ie displays ` rather than #N/A in cells with errors) but its a bulkier and more cumbersome formula than what I remember:

```
=IF(ISERROR(
VLOOKUP(VALUE(TRIM(SUBSTITUTE(S17,"G",""))),Table,2,0)),"`",
VLOOKUP(VALUE(TRIM(SUBSTITUTE(S17,"G",""))),Table,2,0))
```

Is there a way to simplify that?Thanks

Microsoft Excel

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

Previous questions suggest they use XL 2010. And does anyone use XL 2003 anymore?

@Wayne

Are you asking me?

Are you asking me?

Excel 2003 is still in use, but I don't often see examples now.

A universal solution could be to use Conditional Formatting to hide errors, read this

You can also use ISNA

= IF ( ISNA (your formula ) , " " , your formula )

Remember that errors can often be useful though, if they are hidden then the user may not be aware that an error is occurring.

A universal solution could be to use Conditional Formatting to hide errors, read this

You can also use ISNA

= IF ( ISNA (your formula ) , " " , your formula )

Remember that errors can often be useful though, if they are hidden then the user may not be aware that an error is occurring.

"Yep" Wayne, that is exactly what I was after. I didn't recall that there was an "IF"Yep. Use IFERROR instead of ISERROR...

2010What excel version you are using?

One of the great perks of a subscription to EE: learning about things you never before knew existed. Thanks RoyYou can also use ISNA

= IF ( ISNA (your formula ) , " " , your formula )

__________________________

but what really concerns me

are all the things I don't know

and don't even know I don't know them.

If you are using

2007 or later, you may use theIFERRORfunction as suggested by Wayne.