using functions empty cells

I

inquirer

If I have a column of data that contains some empty cells, how can I use
the average or var functions and have them ignore the empty cells?

I have seen this before but can't find it now so I am hoping someone can
point me to the correct way
Thanks
Chris
 
N

Norman Jones

Hi Inquirer,
how can I use the average or var functions and have them ignore the empty
cells?
Click to expand...

I think that Average and Var ignore empty cells, so use the functions 'as
is'.
 
M

Max

Some thoughts ..

If it's really empty cells, or cells with formulas evaluating to null
strings: ""
think the normal functions would suffice:
=AVERAGE(A1:A5)
=VAR(A1:A5)

If it could involve cells with zeros inputted, or cells with formulas
evaluating to zeros (perhaps the display of zeros have been suppressed ..),
then one way would be to use array-entered* expressions like:
=AVERAGE(IF(A1:A5<>0,A1:A5))
=VAR(IF(A1:A5<>0,A1:A5))
*entered via pressing: CTRL+SHIFT+ENTER
 
I

inquirer

Biff said:
Hi!

Both of those functions will ignore empty cells.

Biff
Click to expand...
Thanks for your comments.
The formulae Max gave were the ones I had seen before.
Chris
 
Top