formula Average

[Marky]

If got an excelsheet with 7 different numbers in 7 cells in each cell one number.
If I want to know the average i'll just use the formula, and all the numbers wil be added and divided by 7 because of the 7 cells, sofar no problem, but I need to keep that range of 7 cells, but sometimes the will only be 5 cells filled withe numbers, an than I still need to know the average of those numbers. But now it has to divid by 5.
How can i create a formula that will adapt to the number of filled cells.

[Aladin Akyurek]

On 2002-02-25 10:50, Marky wrote:
If got an excelsheet with 7 different numbers in 7 cells in each cell one number.
If I want to know the average i'll just use the formula, and all the numbers wil be added and divided by 7 because of the 7 cells, sofar no problem, but I need to keep that range of 7 cells, but sometimes the will only be 5 cells filled withe numbers, an than I still need to know the average of those numbers. But now it has to divid by 5.
How can i create a formula that will adapt to the number of filled cells.

How about:

=SUM(A1:A7)/MAX(1,COUNT(A1:A7))

where A1:A7 is the target range (adapt to suit).

[Mark W.]

Use =AVERAGE(A1:A7) and be done with it!!!
AVERAGE ignores empty cells.


[ This Message was edited by: Mark W. on 2002-02-25 11:01 ]

=SUM(A1:A7)/(7-COUNTBLANK(A1:A7))