Averages

[Morfi]

I have a number of columns with 5 entries. I want to calculate the average of each column however sometimes not all 5 cells will have an entry so I dont want these blank cells included in the average i.e. I dont always want to divide the total by 5. What formula can I use that will only count the average of non blank cells?


Thanks

 

[XOR LX]

Hi.

AVERAGE already ignores blank cells.

Regards

 

[gaz_chops]

Try

=average()

 

[Alwina]

=average(a1:a5) will calculate the average on non blanks. Lets say you have a 2 in A1, A3 en A5 - if you type the formula =average(a1:a5) in A6 your average will be 2.

 

[Alwina]

or =SUM(a1:a5)/COUNTA(a1:a5) where counta will count the non blanks

 

[gaz_chops]

or =SUM(a1:a5)/COUNTA(a1:a5) where counta will count the non blanks

Beware of any cells that may have text in them.