How to caluculate average in excel including blank cells

  • Thread starter Thread starter result
  • Start date Start date
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
 
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.
 
Back
Top