We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
10 days, 22 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Simplest way to change what is displayed when an Excel formula results in an error
Posted on 2016-08-17
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):
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)
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))
Thanks
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
2
- +1
6 Comments
Yep. Use IFERROR instead of ISERROR...
=IFERROR(VLOOKUP(VALUE(TRI
=IFERROR(VLOOKUP(VALUE(TRI
Active today
What excel version you are using?
If you are using 2007 or later, you may use the IFERROR function as suggested by Wayne.
If you are using 2007 or later, you may use the IFERROR function as suggested by Wayne.
Active today
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.
Active today
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 ISNAOne of the great perks of a subscription to EE: learning about things you never before knew existed. Thanks Roy
= IF ( ISNA (your formula ) , " " , your formula )
__________________________
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.
Featured Post
Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month10 days, 22 hours left to enroll
631 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.