assigning "missing value" to cells

C

Clare

I have missing values in my data, which appear as blank cells. When I apply
formulae across these cells, it automatically assigns a 0 value to these
cells and includes them in the formula calculations.

I want these cells to be disregarded when I apply a formula to large
sections of data and to remain blank after the formula is dragged acros them.
I can't get this to work at the moment and have to go through my data
manually checking which values were blank, and then deleting the appropriate
cells.

Is there a formula / symbol that tells excel that a blank cell is missing
data and should not be included in subsequent analyses??

Thanks for any suggestions!!
 
S

Sandy Mann

Clare,

I don't fully understand exactly what you mean and I think that it depends
on what formulas you are using. To keep it simple, if the formula is a
simple SUM(), and, say, it is spead across columns F:J as in =SUM(F1:F3)
then test for empty cells like:

=IF(SUM(F1:F3)=0,"",SUM(F1:F3))

or if you only want to test for one empty cell something like:

=IF(F1="","",SUM(F1:F3))

Then drag across the other columns.

HTH

Sandy
 
Top