Average Error

[Guest]

I got some help yesterday with an average issue.
I'm attempting to average specific cells not in a range while ignoring div/o
errors.
This is what I'm using
=AVERAGE(if(ISERROR(AC4,AC40,AC85,AC94,AC121,AC139,AC175,AC202,AC220,AC247,AC274),"",(AC4,AC40,AC85,AC94,AC121,AC139,AC175,AC202,AC220,AC247,AC274))(AC4,AC40,AC85,AC94,AC121,AC139,AC175,AC202,AC220,AC247,AC274))
I can't make it work. What's wrong with it?
Thanks,
Tammie

 

[Guest]

Well, a number of things.

1) You repeat this string of cells twice:
(AC4,AC40,AC85,AC94,AC121,AC139,AC175,AC202,AC220,AC247,AC274)) What are you
trying to accomplish there?

2) Your parentheses are screwed up.

I would start from scratch and suppress div/0 errors in the range you're
trying to average.

Example:

Assume the 0 values come from column B.

In column 1 you would want something like =IF(B1=0,"",C1/B1) or
=IF(ISERROR(C1/B1),"",C1/B1).

This way, you can just use =AVERAGE(AC4:AC274) and AVERAGE will ignore the
blank values.

Dave

 

[Domenic]

As Dave has already mentioned, it would be best to trap the errors in
the first place, if possible. Otherwise, try...

AE4:

=SUMPRODUCT(SUMIF(INDIRECT({"AC4","AC40","AC85","AC94","AC121","AC139","A
C175","AC202","AC220","AC247"}),"<>#DIV/0!"))

AF4:

=SUMPRODUCT(COUNTIF(INDIRECT({"AC4","AC40","AC85","AC94","AC121","AC139",
"AC175","AC202","AC220","AC247"}),{"<0";">=0"}))

AG4:

=AE4/AF4

Hope this helps!