08 November, 2021

CELLS: Blank, Empty, Space, Zero

Back to basics... 

What's the meaning of all these basic terms in Excel?

BLANK cell: empty or not? Not quite, may contain "" (zero length text string)

EMPTY cell: nothing is there (no text string, no zero); always evaluates to zero

Cell with SPACE: contains 'space' character (=" ")

Do you know that: 

  • Cell containing Text can never equate to a cell containing Number (""<>0), even if formatted as Number.
  • Value of a cell with zero length text string is 'worth' more than zero ("">0), i.e. not equal to zero.
  • Function ISTEXT confirms that EMPTY cell is 'worth' more than zero (>0), i.e. not equal to zero, as well.
  • Both EMPTY cells and cells with zero length text strings are counted in Excel as BLANK cells.
  • Cell with '0' value is not BLANK, but equal to EMPTY cell. E.g., if A2 contains 0, and cell A3 is EMPTY, then formula =A3=A2 results in TRUE.

It's very important to be aware of all those definitions and statements when using Excel, especially when working with statistical functions, like Average, Max, Min, STDEV,  etc.. It's quite easy to draw wrong conclusions about your data set. Avoid mixing the blank and empty cells, or text and space characters, with numbers.

No comments:

Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.