Spotting problems with IS functions

Flag missing values, numbers stored as text and broken formulas in an imported stock count. Intermediate · 5 min · ISBLANK, ISTEXT, ISERROR, ISNUMBER, IF + OR

Imported data rarely fails loudly. A count that arrived as text looks just like a number, an empty cell hides in a long column, and one #VALUE! can spoil every total that touches it. The IS functions ask what a cell really holds, and answer TRUE or FALSE:

ISBLANK(B2): is the cell completely empty?
ISTEXT(B2) and ISNUMBER(B2): is it text, or a real number?
ISERROR(D2): does it hold an error of any kind, such as #VALUE! or #N/A?

This stock count came from a warehouse scanner app. You'll build a flag for each kind of problem, then combine them into one column that tells a person which rows to check.

Steps

Start with the gaps. In E2:E11, use ISBLANK to flag every item that has no count in column B.
Some counts arrived as text. They look like numbers, but they sit on the left of their cells. In F2:F11, flag them with ISTEXT.
Column D multiplies each count by its unit cost, and two rows have broken. In G2:G11, flag every value that is an error with ISERROR.
Now turn the question round: a usable count is a real number. In H2:H11, test each count with ISNUMBER.
Finally, one column a person can scan. In I2:I11, show Check when the count is not a number or the value is an error, and nothing ("") otherwise.

Remember

ISBLANK, ISTEXT, ISNUMBER and ISERROR each answer TRUE or FALSE about what a cell holds.
Numbers stored as text still multiply, but SUM and COUNT skip them.
Testing for what you want, with ISNUMBER, catches several problems at once.
Combine the tests with OR inside IF to get a single "Check" column.

Previous: Standard deviation and z-scores · Next: Finding duplicates