ASAP Utilities

Hide formula errors like N/A or #VALUE!

by Bastien 5 Comment

A question I received today from Daniel (Mexico City) :

I will like to suggest you the possibility of including in the formula menu a funtion like "Hide Formula Errors" to stop the anoing NA or VALUE?
Thanks for making this kind's of tools for excel, from now on hopefully I will spend less time in making my complex spreadsheets.

Perhaps I should give this a better place in the ASAP Utilities since it is already there, only I've called it a little different:
ASAP Utilities » Range » Custom formula error message

What it actually does is it builds an =IF(ISERROR()) around it.

Say your formula is =VLOOKUP(B3,F1:G11,2,FALSE), but a corresponding value was not found:

Let's say we want in this case the formula's with an error produce the result zero (0) in stead of the error.

What you can do is build an =IF(ISERROR(old_formula),[value in case of error],old_formula) around it, either manually or with the help of ASAP Utilities.

ASAP Utilities » Range » Custom formula error message

This will replace the original formula with the new error-catching version:
=IF(ISERROR((VLOOKUP(B3,F1:G11,2,FALSE))),0,(VLOOKUP(B3,F1:G11,2,FALSE)))
The main advantage of ASAP Utilities in this case is it's easier and quicker to use because you don't have to type the formula. Plus it can be used in a range of cells with different formula's.

More information about hiding errors on MS Office Online:
http://office.microsoft.com/en-us/assistance/HP030561211033.aspx

Related Posts

5 comments

Headtoadie

I had no idea this was in ASAP. Perhaps it should be under the Formula menu? I use IS(ERROR)) all the time, this will save me much work. This is great!!

Headtoadie

sorry…IF(ISERROR())

Bastien

Hello Headtoadie,

The upcoming months I'm going to create an ASAP Utilities user guide that will list and explain more about the utilities and how to use them. I will post previews/concept-version of the document here.

Keep visiting this blog. I try to write a few examples and answers to questions here every week.

P. Mohan Rao

It is an excellent feature which saves a lot of time and the good thing of this feature is that the original data is not replaced and it is only a display change.

John Doe

Thanks! I have been wrecking my brain on how to solve this problem. I have been trying different if statements, but I was not aware of the ISERROR one. You saved me a whole lot of work!