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.



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?

--

Dave Peterson

=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?

>=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" <(E-Mail Removed)> wrote in message
news:7A8D82DA-5E0C-492A-BD80-(E-Mail Removed)...
> =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?

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" <(E-Mail Removed)> wrote in message
news:eAwr%(E-Mail Removed)...
> >=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" <(E-Mail Removed)> wrote in message
> news:7A8D82DA-5E0C-492A-BD80-(E-Mail Removed)...
>> =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?
>
>