Solved

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

Posted on 2016-08-17
6
57 Views
Last Modified: 2016-08-18
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):

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

Open in new window

However, the error cells currently display #N/A and 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:
=IF(ISERROR(
VLOOKUP(VALUE(TRIM(SUBSTITUTE(S17,"G",""))),Table,2,0)),"`",
VLOOKUP(VALUE(TRIM(SUBSTITUTE(S17,"G",""))),Table,2,0))

Open in new window

Is there a way to simplify that?

Thanks
0
Comment
Question by:WeThotUWasAToad
6 Comments
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 500 total points
ID: 41760414
Yep. Use IFERROR instead of ISERROR...

=IFERROR(VLOOKUP(VALUE(TRIM(SUBSTITUTE(S17,"G",""))),Table,2,0), "`")
1
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41760469
What excel version you are using?
If you are using 2007 or later, you may use the IFERROR function as suggested by Wayne.
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41760474
Previous questions suggest they use XL 2010. And does anyone use XL 2003 anymore?
0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41760480
@Wayne
Are you asking me?
0
 
LVL 18

Expert Comment

by:Roy_Cox
ID: 41760577
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.
0
 

Author Comment

by:WeThotUWasAToad
ID: 41761753
Yep. Use IFERROR instead of ISERROR...
"Yep" Wayne, that is exactly what I was after. I didn't recall that there was an "IF" and "IS" flavor so I was looking for something else. I'll never forget again though, after seeing them side-by-side. Many thanks.

What excel version you are using?
2010

You can also use ISNA
= IF ( ISNA (your formula ) , " " , your formula )
One of the great perks of a subscription to EE: learning about things you never before knew existed. Thanks Roy

_____________________________________________________
I worry about all the things I know I don't know,
but what really concerns me
are all the things I don't know
and don't even know I don't know them.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question