Average formula where blank cells are counted as zeros

  • Thread starter Thread starter krwelling
  • Start date Start date
K

krwelling

I am trying to write an average formula that takes into account the blank
cells.

I had only cells A2, A4, A6 filled out, but when I do =average(A1:A6), it is
not treating the blanks as zeros so the answer is much higher than it should
be.

Should I do a logic formula within the cells?
 
If you know the range, you could do the division yourself:

=sum(a1:a6)/6

But this would include other non-numeric cells in the count of cells (6), too.
 
=AVERAGE(IF($A$2:$A$6<>0,$A$2:$A$6)) AND HIT Ctrl+Shift+Enter to enter the
array formula.
 
=AVERAGE(IF($A$2:$A$6<>0,$A$2:$A$6))

I think the OP wants to include empty cells and evaluate them as numeric 0.

Try one of these this array formulas** .

This one assumes the range contains numbers or empty cells only. No TEXT, no
formula blanks "".

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

This one will account for (ignore) TEXT but will evaluate blank/empty cells
as numeric 0.

=AVERAGE(IF(ISNUMBER(A2:A6),A2:A6,IF(A2:A6="",0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Or, it might be as simple as:

=SUM(A2:A6)/ROWS(A2:A6)

We'd need more details as to what the possible entries are in the range.
 

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

Back
Top