Understand greatest Value in using ISNUMBER & NA/IF-ERROR

[brian sullivan]

Greetings,

I have a pretty in-depth knowledge of ISNUMBER & NA integrated into formulas.

IsNUMBER- identifies non numerical values in a data. Curious in knowing any other strong purposes here?

NA/IFERROR- How much of the time is NA really necessary in formulas & besides trapping errors does it have any other purposes? I'm curious In knowing if it's more like a data safety valve when looking up values (in huge data ranges).

=MIN(IF(ISNUMBER('2014 data'!L4:L10),IF('2014 data'!G4:G10=C7,IF('2014 data'!$H$4:$H$10=E7,IF('2014 data'!$I$4:$I$10=F7,IF('2014 data'!$J$4:$J$10=G7,'2014 data'!$L$4:$L$10))))))

=MIN(IFERROR(('2014 data'!$L$4:$L$10)*IF('2014 data'!$G$4:$G$10=C7,1,NA())*IF('2014 data'!$H$4:$H$10=E7,1,NA())*IF('2014 data'!$I$4:$I$10=F7,1,NA())*IF('2014 data'!$J$4:$J$10=G7,1,NA()),FALSE))

 

[NARAYANK991]

Hi Brian ,

Let us consider your first formula :

=MIN(IF(ISNUMBER('2014 data'!L4:L10),IF('2014 data'!G4:G10=C7,IF('2014 data'!$H$4:$H$10=E7,IF('2014 data'!$I$4:$I$10=F7,IF('2014 data'!$J$4:$J$10=G7,'2014 data'!$L$4:$L$10))))))

The MIN function anyway ignores text values when looking for the least value. Thus , the check for whether a data item in a range is a numeric value or not is not really necessary. What the ISNUMBER function does in this specific case , is to eliminate error values from being considered.

If you did not have the ISNUMBER function check , and if the range L4 : L10 contained any error values , then the MIN function would also return an error value ; including the ISNUMBER function check ensures that such error values are ignored , and the MIN function is able to look at the non-error values and return the lowest value correctly.

In this specific case , the more appropriate function would have been NOT(ISERROR(...)) , but since this uses 2 functions , the ISNUMBER check is in fact more efficient.

Narayan

 

[Luke M]

Hi Brian,

Good question! All of the IS___ functions are designed to test for something specific. Knowing which one to use requires knowing what outputs can happen. This in turn can require an understanding of what the different error values mean. At a basic level, there's a clear difference between these errors:
#VALUE!
#DIV/0!
#REF!

And seeing any of these should instantly clue you in to what is wrong with the formula. With the NA error, we note that it means "not applicable". This can help us figure out what when doing a lookup type operation and the value isn't found. The big quicker, IMO, is when charting. If you plot an NA value, the chart will skip over it. See the difference:

Generally, this is the only case where I purposely generate a NA error.

ISNUMBER is a good function to use when you need to filter out non-numerical values, such as when you're about to perform a mathematical operation (like in your formula).

PS. If you want to simplify the formula, you can reduce it to:
=MIN(IF(ISNUMBER('2014 data'!L4:L10),('2014 data'!G4:G10=C7)*('2014 data'!$H$4:$H$10=E7)*('2014 data'!$I$4:$I$10=F7)*('2014 data'!$J$4:$J$10=G7)*('2014 data'!$L$4:$L$10)))

Note that we don't need any error value trapping. The IF function will return FALSE for anything that's not a number in L4:L10, which gets ignored by the MIN function.