Conditional Average

[robotman]

How can I use a formula to average values in rows which have a certain
value in another column of that same row?

For example, I want to average the values in Col A if Col D = "A":

A B C D
1 12 1234 1234 A
2 11 1232 1654 A
3 40 2312 5435 B


This is what I tried (entered as an array with CTRL-SHIFT-ENTER), but
I get a #VALUE error:
=AVERAGE(IF(D25000="A",A2:A5000,""))

The answer I'd be looking for is 11.5.

Any ideas?

Thanks.

John

 

[Guest]

The average won't work with empty cells or zeros. Your range extends beyond
the data.

 

[Guest]

I'm not sure that's correct. AVERAGE works fine on my XL with empty cells,
whether entered as an array or otherwise.

Are the numbers formatted as numbers or text?

Dave

 

[Bob Phillips]

Works fine if you properly array enter it.

--
HTH

Bob

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

 

[robotman]

I took the "" out of the false part of the IF statement and it is now
working. If I don't enter as an array, I just get a 0.

Is the formula I used the best way to do this? Any way to do it
without an array?

Thanks.

John