Missing Values In Excel

Empty cells are treated as 0 when:

• you do simple arithmetic, eg 9 – blank = 9

Empty cells are treated as missing when:

• you do calculations using Excel statistical

functions, eg average(9,blank) = 9

If the arithmetic

involves just 1 cell

(eg =log(cell)), then

you can check the

contents of this cell

with the “If” function

and one of several choices of information functions which return a true of false result, eg

=if(isnumber(cell),log(cell),””)

The if function has the form

check that all cells

have a value using

the count function.

Two double quotes (“”)

na()

displays a blank in the cell

displays #N/A in the cell

is treated as a blank cell in arithmetic and

statistical functions

results in an error in arithmetic and

statistical functions

Plots as zero on graphs for y variable

does not show on graphs

To get a missing value resulting from a formula to NOT display as 0 on graphs

You can replace “” in the “if” formula with na() eg =if(isnumber(cell),log(cell),na()).

Note that if you copy the calculated cells and paste as values, the cells that had the “”

result appear to be empty but still show on graphs as zeros!! You can find these cells by

selecting the range containing the calculated cells, choosing Edit/Goto/Special and

selecting Constants and Text then hitting the Delete key. (Let’s hope it is better in the

next version!)

Neil Cox, Statistics, AgResearch Ruakura