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.
 
Back
Top