Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Computing an average that excludes error message

100 views
Skip to first unread message

Elie Yehezkel

unread,
Jul 23, 2003, 4:59:22 PM7/23/03
to
Some of the cells in a column returns a #VALUE! error
message.
I am trying to come up with a formula that excludes the
error message when calculating the average of that column.
There is a way to exclude zeros using the following array
formula:
{=AVERAGE(IF(b2:b9<>0,B2:B9))}
So I have applied the above formula to exclude the #VALUE!
error message, but it didn't work, it returned a #Value!
error message.

Please any suggestions?

Elie

Qingde

unread,
Jul 23, 2003, 5:14:18 PM7/23/03
to
You may try to use the logical function iserror() in your
formula.

>.
>

Ron de Bruin

unread,
Jul 23, 2003, 5:28:10 PM7/23/03
to
Posted by Harlan Grove

ISERR catches error values excluding #N/A while ISERROR catches error values
including #N/A. I'd think the OP would prefer ISERROR (once translated to
German). To exclude error values and zeros, try the array formula

=AVERAGE(IF(ISNUMBER(1/A1:A29),A1:A29))
Also a Array formula

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl

"Elie Yehezkel" <yehezk...@hotmail.com> wrote in message news:00cf01c3515d$4acdb730$a601...@phx.gbl...

Ron Rosenfeld

unread,
Jul 23, 2003, 5:31:39 PM7/23/03
to
On Wed, 23 Jul 2003 13:59:22 -0700, "Elie Yehezkel" <yehezk...@hotmail.com>
wrote:

To just exclude error messages, array-enter:

=AVERAGE(IF(ISNUMBER(A1:A8),A1:A8,""))

Note that YOU do not enter the braces; XL does that when you do
<ctrl><shift><enter>.

To exclude both error messages and 0's, array-enter:

=AVERAGE(IF(ISNUMBER(A1:A8),(A1:A8<>0)*A1:A8,""))


--ron

0 new messages