ISNUMBER()

Syntax. ISNUMBER(value)

Definition. This function returns TRUE if the value argument is evaluated to a number (numeric value) without being converted. Otherwise, the function returns FALSE.

value (required). The expression (a number, text, a formula without an equal sign, a logical value, an error value, a reference, or a name) that you want to check

Background. This function is one of the nine IS() functions that return a logical value depending on the argument. The argument of the IS() functions is not converted for evaluation. This means that a string consisting of a number is interpreted as a string (not as a number). IS() functions are often used together with the IF() function to pre-test the result of a calculation. The result returned by an IS() function can be used as the basis for conditional formats and validation rules.

Note that this function returns FALSE if the value argument refers to an empty cell. But, if you create a reference to an empty cell, the cell containing the reference shows the value 0.

To avoid this, on the Advanced tab of the Excel Options dialog box, select Show A Zero In Cells That Have Zero Value (in Excel 2007 and Excel 2010), or select Tools/Options and select the Zero Values Disabled check box on the View tab (in previous versions of Excel). However, the calculation still uses 0.

Example. In the example for the ISTEXT() function, the formula to calculate the sales tax was entered in column N (see Figure 11-6 later in this chapter):

=IF(ISTEXT(M42),,L42*M42)

If no sales tax is used, the value 0 is displayed. If you want to show an empty cell instead, enter the following formula:

=IF(ISTEXT(M42),"",L42*M42)

However, the formula

=L42+N42

to calculate the gross amount produces an error. To avoid this, use the ISNUMBER() function together with the IF() function:

=IF(ISNUMBER(N42),L42+N42,L42)

See Also

ISBLANK(), ISERR(), ISERROR(), ISLOGICAL(), ISNA(), ISNONTEXT(), ISREF(), ISTEXT()