View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
T. Valko T. Valko is offline
external usenet poster
Posts: 15,768
Default Average formula where blank cells are counted as zeros

Or, it might be as simple as:


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

Microsoft Excel MVP

"T. Valko" wrote in message

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

Try one of these this array formulas** .

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


This one will account for (ignore) TEXT but will evaluate blank/empty
cells as numeric 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.

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
array formula.

"krwelling" wrote:

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

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 I do a logic formula within the cells?