Ignore blank cells in a average

[g29115]

All,

I am doing an average of a column of cells. How do I tell excel to ignore the blank cells in the column and not calculate them in the average?

G.

 

[Jonmo1]

That is not true if all cells are blank in range.

Actually, it is still true.
I assume the error you get is #DIV/0! when ALL the cells are blank.
Average = sum/count
If you're getting #DIV/0! error, that means there was a 0 count, therefor it DID in fact ignore (did not count) the blanks.
The count was 0, therfor the average was basically 0/0 = #DIV/0!

Which version of Excel are you using?
If you have XL2007+ you can do
=IFERROR(AVERAGE(A:A),"")

or in XL2003 try
=IF(COUNT(A:A,AVERAGE(A:A),"")

 

[hsg1953]

Jonmo1,

thank you that did resolve this. I am using Office 2010.

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[PENTAPOST1]

I get the #DIV/0! when I use the average in a column that has blank cells.

 

[shg]

What formula are you using?

 

[PENTAPOST1]

I was using the avg tab linked to the "AUTO SUM". However I read a little further in the thread and the formula IFERROR(AVERAGE(A1:A1),"") was suggested. this solved the problem of having blank cells in a column where an average is needed.

 

[joshmd]

That is not true if all cells are blank in range.

I have a range of cells used for Status averages. Initially each week all cells are blank and then filled in as tasks are completed or partially.
I want an average of all non-blank cells at the top but using any of the formulas given here, there is an error when all cells are blank. Any input?

Why not try AverageIF - that would avoid the array command (ctrl-shift-enter "{ }"). Average does not include blank cells in its calculation and the IF statement can exclude a certain criteria (for example, to avoid less than 0 values enter "=AVERAGEIF(Range, "<0")

 

[Gregorio]

Useful thread, just had the same question and found it solved already