I'm using EXCEL office 2003. I'm trying to average from several cells (which
have linked information from other pages), some of the cells have 0 in them.
I've looked up all the ways to have 0 not be included, and it works on all
the averages except when I'm bringing information from other pages. Since
this is a calendar, many of the months have no value. I've tried average IF
formula, using conditioning and formating page so 0 don't show, but no
success. Any other thoughts?

You don't say what you have tried, but the normal way is

=AVERAGE(IF(A2:A20>0,A2A20))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"RADIANT JILL" <RADIANT (E-Mail Removed)> wrote in message
news:7ACEFD22-8A8E-4923-8019-(E-Mail Removed)...
> I'm using EXCEL office 2003. I'm trying to average from several cells
> (which
> have linked information from other pages), some of the cells have 0 in
> them.
> I've looked up all the ways to have 0 not be included, and it works on all
> the averages except when I'm bringing information from other pages. Since
> this is a calendar, many of the months have no value. I've tried average
> IF
> formula, using conditioning and formating page so 0 don't show, but no
> success. Any other thoughts?

Just in case you have not spotted the missing colon ":" in the range setting
that Bob supplied the formula is

=AVERAGE(IF(A2:A20>0,A2:A20))

enter as Array formula.

--

Regards,
Nigel
(E-Mail Removed)



"Bob Phillips" <(E-Mail Removed)> wrote in message
news:O8tQG$(E-Mail Removed)...
> You don't say what you have tried, but the normal way is
>
> =AVERAGE(IF(A2:A20>0,A2A20))
>
> which is an array formula, it should be committed with Ctrl-Shift-Enter,
> not just Enter.
> Excel will automatically enclose the formula in braces (curly brackets),
> do not try to do this manually.
> When editing the formula, it must again be array-entered.
>
> Note that you cannot use a whole column in array formulae (prior to excel
> 2007), but must use an explicit range.
>
> --
> ---
> HTH
>
> Bob
>
>
> (there's no email, no snail mail, but somewhere should be gmail in my
> addy)
>
>
>
> "RADIANT JILL" <RADIANT (E-Mail Removed)> wrote in message
> news:7ACEFD22-8A8E-4923-8019-(E-Mail Removed)...
>> I'm using EXCEL office 2003. I'm trying to average from several cells
>> (which
>> have linked information from other pages), some of the cells have 0 in
>> them.
>> I've looked up all the ways to have 0 not be included, and it works on
>> all
>> the averages except when I'm bringing information from other pages.
>> Since
>> this is a calendar, many of the months have no value. I've tried average
>> IF
>> formula, using conditioning and formating page so 0 don't show, but no
>> success. Any other thoughts?
>
>

oops, thanks for that.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Nigel" <nigel-(E-Mail Removed)> wrote in message
news:1D3BAE38-13D4-4DD8-8E63-(E-Mail Removed)...
> Just in case you have not spotted the missing colon ":" in the range
> setting that Bob supplied the formula is
>
> =AVERAGE(IF(A2:A20>0,A2:A20))
>
> enter as Array formula.
>
> --
>
> Regards,
> Nigel
> (E-Mail Removed)
>
>
>
> "Bob Phillips" <(E-Mail Removed)> wrote in message
> news:O8tQG$(E-Mail Removed)...
>> You don't say what you have tried, but the normal way is
>>
>> =AVERAGE(IF(A2:A20>0,A2A20))
>>
>> which is an array formula, it should be committed with Ctrl-Shift-Enter,
>> not just Enter.
>> Excel will automatically enclose the formula in braces (curly brackets),
>> do not try to do this manually.
>> When editing the formula, it must again be array-entered.
>>
>> Note that you cannot use a whole column in array formulae (prior to excel
>> 2007), but must use an explicit range.
>>
>> --
>> ---
>> HTH
>>
>> Bob
>>
>>
>> (there's no email, no snail mail, but somewhere should be gmail in my
>> addy)
>>
>>
>>
>> "RADIANT JILL" <RADIANT (E-Mail Removed)> wrote in message
>> news:7ACEFD22-8A8E-4923-8019-(E-Mail Removed)...
>>> I'm using EXCEL office 2003. I'm trying to average from several cells
>>> (which
>>> have linked information from other pages), some of the cells have 0 in
>>> them.
>>> I've looked up all the ways to have 0 not be included, and it works on
>>> all
>>> the averages except when I'm bringing information from other pages.
>>> Since
>>> this is a calendar, many of the months have no value. I've tried
>>> average IF
>>> formula, using conditioning and formating page so 0 don't show, but no
>>> success. Any other thoughts?
>>
>>
>