How to caluculate average in excel including blank cells

[Mike H]

Try

=AVERAGE(IF(A2:A5,A2:A5,A2:A5))

This is an array formula and must be entered by pressing CTRL+Shift
+Enter
and NOT just enter. If you do it correctly then Excel will put curly
brackets
around the formula {}. You can't simply type these yourself. Change
D20, O20
to the range you want but you then must re=enter as an array.

Mike

 

[Mike H]

Hmmm,

Pasted in the wrong array instructions

This is an array formula and must be entered by pressing CTRL+Shift
+Enter and NOT just enter. If you do it correctly then Excel will put curly
brackets around the formula {}. You can't simply type these yourself. Change
the range to the range you want but you then must re-enter as an array.

 

[muddan madhu]

try this

=SUM(A1:A4)/ROWS(A1:A4)