Engineers-Excel.com

Distinguishing a blank cell from a cell containing zero



One of my colleagues had to detect if a cell had a non-negative value. The difficulty was that if he used the formula =if(A1<0,"Negative","Positived or zero") to check for the contents of A1, it would return Positive or zero when the cell was blank. The user wanted the blank cells to be ignored. However, a blank cell has a value of 0 by default, and the formula would take in this value during evaluation. How could a blank cell be detected ?

Detecting blank cells is quite straightforward, but many users are not aware of how it can be done. The type of contents in a cell can be detected using the functions under the "information functions" in Excel. The formuale in this category can be used to detect if a certain cell is blank, or if it has a number or if it has an error.

Accordingly, the formula to detect whether a cell contains a positive value, a negative value or is blank would be:

=if(ISBLANK(A1),"Blank",if(A1<0,"Negative","Positive or zero"))

This formula worked for the user because the cell would only contain a number or be blank. But this formula would fail if the cell contained text. To check if a cell contains text, the function ISTEXT could be used.

Another common use of the information functions is for detecting whether a cell contains an error (#VALUE!, #NUM!, etc.). The function ISERROR can be used to detect if a cell contains an error.