Average Error
 

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))(A C4,AC40,AC85,AC94,AC121,AC139,AC175,AC202,AC220,AC 247,AC274))
I can't make it work. What's wrong with it?
Thanks,
Tammie

Average Error
 

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

--
Brevity is the soul of wit.


"tamiluchi" wrote:

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))(A C4,AC40,AC85,AC94,AC121,AC139,AC175,AC202,AC220,AC 247,AC274))
I can't make it work. What's wrong with it?
Thanks,
Tammie

Average Error
 

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","A C94","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!

In article ,
tamiluchi wrote:

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,AC2
74),"",(AC4,AC40,AC85,AC94,AC121,AC139,AC175,AC202 ,AC220,AC247,AC274))(AC4,AC4
0,AC85,AC94,AC121,AC139,AC175,AC202,AC220,AC247,AC 274))
I can't make it work. What's wrong with it?
Thanks,
Tammie