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.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Index/match - make blank cells return a blank value. 2
Entering an If formula using conditional formatting 1
Excel Average dates help 0
calculate total number of items that meet 2 over multiple sheets 5
Create a simple average that does NOT include blanks cells 4
Need a formula to calculate greater than & show answer as a % 2
average in non-continuous set, excluding blanks 6
Blank cell with average formula 1

Back
Top