Average formula where blank cells are counted as zeros
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.
--
Biff
Microsoft Excel MVP
"T. Valko" wrote in message
...
=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.
--
Biff
Microsoft Excel MVP
"Nadine" wrote in message
...
=AVERAGE(IF($A$2:$A$6<0,$A$2:$A$6)) AND HIT Ctrl+Shift+Enter to enter
the
array formula.
"krwelling" wrote:
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?
|