Top 12 Excel Combinations: 9. NOT ISNUMBER

For 2024, I thought I would count down a very subjective "Top 12" of Excel function combinations. And this month’s offering makes it very clear that this is a highly subjective list. If you don’t like this month’s suggestion, that’s fine; life would be boring if we were all in agreement.

Continuing our Top 10 countdown, the 9^th of 12 is a combination which may often be swapped for simpler calculations, but is useful nonetheless, in determining whether or not a cell contains a number.

At the time of writing, there are 13 IS functions, ie, functions that give rise to a TRUE or FALSE value depending upon whether a certain condition is met:

1. ISBLANK(reference): checks whether the reference is to an empty cell.
2. ISERR(value): checks whether the value is an error (eg, #REF!, #DIV/0!, #NULL!). This check specifically excludes #N/A.
3. ISERROR(value): checks whether the value is an error (eg, #REF!, #DIV/0!, #NULL!). This is probably the most commonly used of these functions in financial modelling.
4. ISEVEN(number): checks to see if the number is even.
5. ISFORMULA(reference): checks to see whether the reference is to a cell containing a formula.
6. ISLOGICAL(value): checks to see whether the value is a logical (TRUE or FALSE) value.
7. ISNA(value): checks to see whether the value is #N/A. This gives us the rather crude identity ISERR + ISNA = ISERROR.
8. ISNONTEXT(value): checks whether the value is not text (N.B. blank cells are not text).
9. ISNUMBER(value): checks whether the value is a number.
10. ISODD(number): checks to see if the number is odd. Personally, I find the number 46 very odd, but Excel doesn’t.
11. ISOMITTED(argument): checks whether the argument has been omitted in a lambda function.
12. ISREF(value): checks whether the value is a reference.
13. ISTEXT(value): checks whether the value is text.

As stated above, the ISNUMBER function checks to see whether the value is a number. It has the following syntax:

ISNUMBER(value)

It might seem unremarkable, but it is useful for identifying numerical values.

NOT is one of Excel’s logic functions and reverses the logic of its argument. The syntax is as follows:

NOT(logical)

Where logical is the condition whose logic you wish to reverse. Therefore, NOT(ISNUMBER) is typically used to determine when something is not a number.

Consider the following example:

In this illustration, I have totalled the values in cells E3:E7 in two distinct ways: the first uses the aforementioned SUM function with ALT + =, the other has added each cell individually using the ‘+’ operator. Are you thinking you’d be mad to use the alternative (second) approach – especially if there were many more rows?

Well, take another look:

In this example, cell E5 has been modified. It has been stored as text, even though it looks like the number 3. SUM treats this as having zero value whereas the more convoluted addition carries on regardless. Simplest may not always be bestest.

In an example like the one above, this may be easy to spot, but would you stake your life that the sum

is correct?

There is a simple way to check using the COUNT function. COUNT counts the number of numbers in a range, so we can use it to spot numbers that aren’t numbers. For example, in cell G5:

=1-COUNT(E5)

Here, the formula in column I highlights when a number is not a number. Note how it reports by exception: if the cell in question contains a number then COUNT(cell_reference) equals 1 and =1-COUNT(cell_reference) equals zero [0]. Only non-numbers will be highlighted.

NOT(ISNUMBER) is arguably an easier to follow alternative formula for G5:

=NOT(ISNUMBER(E5))*1

(Multiplying by one [1] converts TRUE / FALSE to 1 / 0.)

This idea may be extended for ranges to see how many numbers in a range are not numbers, eg,

Here, cell H3 contains the formula

=SUM(NOT(ISNUMBER(C3:F3))*1)

Again, a COUNT alternative could be constructed, but the combination of NOT with ISNUMBER does indeed seem easier for less experienced Excel users to follow (you don’t need to know COUNT only counts numbers, not other data types).

Counterintuitively, the NOT ISNUMBER combination can be used for non-numerical data too. Consider the following illustration:

Here, the Lookup Value ("Sunday") (cell B1) is sought in the range E1:E12 (the Lookup List). If it exists, the MATCH function will return a numerical value representing the position of the first occurrence in the list. If it does not exist, #N/A will be returned, which is not a number so

=NOT(ISNUMBER(MATCH(B1,E1:E12,0)))

will thus return a value of TRUE, ie, "Sunday" is not contained in the list. You could instead use the formula

=COUNTIF(E1:E12,B1)=0

but the first formula – although longer – seems easier to understand for many.

The Top 10 continues next month.