The Excel ISNUMBER function is an Information formula that returns TRUE if value is a number, and FALSE if not. As the name suggests, the formula asks the question "Is number?" to its argument. This way, you can test the outcome of a formula or a cell value using this function. This formula can be especially useful in analyzing a large data set. In this guide, we’re going to show you how to use the Excel ISNUMBER function and also go over some tips and error handling methods.
Supported Versions
The ISNUMBER function is supported in all versions of Microsoft Excel. This includes both the older versions and the latest releases up to the present day.
Excel ISNUMBER Function Syntax
The structure of the ISNUMBER function is straightforward:
value: This is the only argument for the ISNUMBER function. It represents the value you want to test to determine if it is a number. This "value" can be a direct number, a reference to a cell containing the data, or a result returned by another formula.
When you input a value into the ISNUMBER function, it evaluates that value. If the value is a number, the function returns TRUE. If the value is not a number (like text, an error value, or a blank cell), the function returns FALSE.
Examples of ISNUMBER Function
The ISNUMBER function, just like the other IS… functions, only requires a single argument. A common use case for this function is testing a value of cell or value that is returned by a formula. The function returns TRUE for numeric outcomes, and FALSE otherwise. Please note that date serial values are also considered as numbers by Excel.
When using the ISNUMBER function with a direct number input, such as =ISNUMBER(10), the function evaluates the provided number and returns a result. In this case, since 10 is indeed a numerical value, the function would yield a TRUE result.
When you use the ISNUMBER function on a cell containing a date, the function recognizes the date's underlying serial number. Therefore, it treats the date as a number and returns TRUE. For example, =ISNUMBER("8/25/2020") would return TRUE because "8/25/2020" is internally represented as a serial number (like 44068 in Excel's date system).
If you use a date function that returns a date, such as DATE, and nest it within ISNUMBER, it will also return TRUE. This is because the DATE function outputs a serial number representing the specified date. An example would be =ISNUMBER(DATE(2020,8,25)), which returns TRUE. DATE(2020,8,25) generates the serial number for August 8, 2020, and ISNUMBER recognizes this serial number as a numeric value.
ISNUMBER will return FALSE when evaluating any text string. It doesn't matter what the text is; as long as the input is not a number, the result will be FALSE. For example, =ISNUMBER("Apple") would return FALSE because "Apple" is a text string and not a numeric value.
Use ISNUMBER with IF Function
The fact that the ISNUMBER function outputs either TRUE or FALSE makes it an ideal candidate for use in conjunction with the IF function, catering to a variety of practical scenarios.
Data Validation: Combining the ISNUMBER and IF functions is an important technique in Excel for ensuring the accuracy and appropriateness of the data entered into a spreadsheet. This approach is incredibly useful for confirming whether cells contain numeric values, which is crucial in scenarios where the nature of the data directly affects subsequent computations or analyses. For instance, in a budget sheet where users are expected to input expense figures, it’s vital to verify that these inputs are indeed numeric to avoid errors in calculations.
Consider a situation where you have a financial spreadsheet with an input column (Column A) for expense amounts and a validation column (Column B) to flag incorrect entries. You can use a formula like =IF(ISNUMBER(A1), "", "Invalid Entry") in Column B. This formula checks each entry in Column A; if it’s numeric, Column B remains blank, signaling a valid entry. If a non-numeric value such as text is entered, Column B displays "Invalid Entry". By copying this formula down Column B, you create an efficient, real-time validation system. Such a setup not only provides immediate feedback to users, enhancing data accuracy, but also maintains the integrity of your dataset, which is particularly crucial in financial and statistical data handling.
Error Handling: The combination of ISNUMBER and IF functions can be used to prevent and manage errors. When a formula is set to handle numeric data but receives a non-numeric input, it can lead to errors or incorrect results. By integrating ISNUMBER with IF, you gain control over the outputs, allowing you to either display a custom error message or revert to a default value when non-numeric data is entered.
For example, imagine you have a formula in your spreadsheet that calculates the square root of a number in cell A1 using =SQRT(A1). If someone mistakenly enters text like "Apple" in A1, this would normally result in an error. To handle this, you can modify the formula to =IF(ISNUMBER(A1), SQRT(A1), "Invalid Input"). This revised formula first checks if A1 contains a number. If it does, it proceeds with the square root calculation. If not, it displays "Invalid Input" instead of an error. This approach not only prevents confusing error messages but also guides users towards correcting their input, ensuring that your spreadsheet remains functional and user-friendly.
Use ISNUMBER with SEARCH and FIND Functions
To determine whether a cell includes a certain piece of text (such as a substring), you can combine the ISNUMBER function with the SEARCH and FIND functions. FIND and SEARCH functions operate similarly: the first argument is the text you're looking for, and the second is where you're searching for it. Both FIND and SEARCH give you the position where the searched text first appears. If they can't find the text, they return a #VALUE! error. In simple terms, if they find the text, you get a number indicating where it starts; if they don't, you get an error.
This is where the ISNUMBER function becomes useful. ISNUMBER checks if its input is a number and returns TRUE if it is, or FALSE if it isn’t. To turn a text search into a straightforward YES (TRUE) or NO (FALSE) answer, you can place the FIND or SEARCH function inside ISNUMBER. This combination lets you easily check if the text is present or not.
Error Handling in ISNUMBER Function
The ISNUMBER function itself does not generate errors; instead, it simply evaluates whether the given value is a number, returning TRUE for numeric values and FALSE for anything else, including error values.
Tips
- You can use the Excel ISNUMBER function as part of the logical test of an IF function. =IF(ISNUMBER(A1),"Logical","Not logical")
- Excel keeps date and time values as numbers. Excel assumes that Jan 1st, 1900 is 1, and every subsequent date value is based on this. While whole numbers represent days, decimals represent time values. For example; 1/1/2018 is equal to 43101, and 12:00 is equal to 5. Because of this, the Excel ISNUMBER function counts date time values as numbers.
- IFERROR function can return a specific value if an error occurs.
- Below is a list of other IS… functions:
