Written by Allen Wyatt (last updated April 15, 2022)
This tip applies to Excel 97, 2000, 2002, and 2003
If you need to base a calculation on whether a cell has a number in it or not, you can use the ISNUMBER worksheet function. This function returns True if the target cell contains a numeric value or False if it contains anything else. For instance, if you want to do a calculation based on whether cell A3 contains a number, you could use the following:
=IF(ISNUMBER(A3),(A3*12)/52,"Enter number in cell A3")
This example results in the cell containing the result of (A3 * 12) / 52, but only if A3 contains a number. If it does not (for instance, it is blank or contains text), then the result contains the text "Enter number in cell A3."
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2113) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Checking for a Value in a Cell.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
Need to get rid of spaces in a range of cells? There are two ways you can approach the task, as described here.
Discover MoreBefore you go about deleting rows and columns helter-skelter, it is a good idea to determine if there is anything in the ...
Discover MoreWant to select all the data in a contiguous section of a worksheet? The shortcut discussed in this tip makes it very easy.
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-09-17 20:20:01
Jay Bingham
This is good advice as far as it goes, however, suppose that you have a formula that can produce either a number or a blank cell and you have a formula in a second cell that references the first cell but it will produce an error if the formula in the first cell does not produce a number. Neither the ISNUMBER nor the ISBLANK function are of any use in this case, because contents of the first cell is neither a number or a blank.
I have found that the MAX and the MIN functions can be used to determine if the referenced cell actually contains a number.
For example if cell C4 contains this formula =IF(SUM(D24:D34),SUM(D24:D34),"") and in another cell, lets say C22, you want to determine if cell C4 resolves to a number rather than a blank so that the formula in C22 will not produce an error if C4 resolves to a blank then the formula in C22 can use the MAX function to make that determination, e.g. =IF(AND(MAX(C4)>0,C13>0),C4/C13,"").
There may be other ways to determine this but I stopped looking when I found that MAX and MIN would do the trick.
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments