AVERAGE FORMULA PROBLEM
 

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?

AVERAGE FORMULA PROBLEM
 

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

=AVERAGE(IF(A2:A200,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 wrote in message
...
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?

AVERAGE FORMULA PROBLEM
 

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

=AVERAGE(IF(A2:A200,A2:A20))

enter as Array formula.

--

Regards,
Nigel




"Bob Phillips" wrote in message
...
You don't say what you have tried, but the normal way is

=AVERAGE(IF(A2:A200,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 wrote in message
...
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?