Average only if there is data

[That's Confidential]

I am trying to work out the average of the numbers in a column and so have
entered the following formula

=AVERAGE(A1:40)

This however does not work for me. I only want it to work out the average if
there is a value in the cells in the column. As it stands now, I am entering
a few values between 1 - 5 in the cells in column A, however as I am missing
out some cells out too, I am ending up with a figure of 0.80.

So, how do I work out the average of the values of cells in a column,
excluding cells within that column with no data in them?

Thanks

 

[RagDyer]

The Average function does *not* include empty cells in it's calculations.
However, it *does* include zeroes.

So, I would guess that some of your cells possibly contain 0.

If you don't see any, is it possible that you have the display of 0's turned
off?
<Tools> <Options> <View> tab,
and see if zero values is checked.

If, on the other hand, you know that there are zero entries, and you *don't*
want them to enter into the calculation of your averages, then you should
use the Sum function divided by the Countif function.

=SUM(A1:A40)/COUNTIF(A1:A40,">0")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I am trying to work out the average of the numbers in a column and so have
entered the following formula

=AVERAGE(A1:40)

This however does not work for me. I only want it to work out the average if
there is a value in the cells in the column. As it stands now, I am entering
a few values between 1 - 5 in the cells in column A, however as I am missing
out some cells out too, I am ending up with a figure of 0.80.

So, how do I work out the average of the values of cells in a column,
excluding cells within that column with no data in them?

Thanks

 

[Charlie]

The =AVERAGE(A1:A40)works fine for me as long as the empty
cell's are really empty. If there is a 0 (zero) then the
0 (zero) is part of the average.

You could try =SUM(A1:A40)/COUNTIF(A1:A40,">0") it's not
fancy but it works.

Charlie O'Neill

 

[Jerry W. Lewis]

If your cells "with no data in them" contain formulas, have the formulas
return "" instead of zero when there is "no data"

Jerry