AVERAGE FORMULA PROBLEM

[RADIANT JILL]

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?

 

[Bob Phillips]

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)

 

[Nigel]

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 address removed)

 

[Bob Phillips]

oops, thanks for that.

--
---
HTH

Bob


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