assigning "missing value" to cells

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!!

Hi!

1. You say you go to the blank cells and delete them. At least you could use F5 > Special > Blanks to show you all of the blank cells.

2. Which functions are you having trouble with? Some, such as Average, and Sumdo it anyway.


Alf

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

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"Clare" <Clare@discussions.microsoft.com> wrote in message
news:A44AB3A9-E356-4000-99B5-2FA3AB017CA4@microsoft.com...
> 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!!