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(D2:D5000="A",A2:A5000,""))

The answer I'd be looking for is 11.5.

Any ideas?

Thanks.

John

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

--
Anything is possible if you do not know what you are talking about.


" wrote:

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(D2:D5000="A",A2:A5000,""))

The answer I'd be looking for is 11.5.

Any ideas?

Thanks.

John

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Wood Grafing" wrote:

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

--
Anything is possible if you do not know what you are talking about.


" wrote:

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(D2:D5000="A",A2:A5000,""))

The answer I'd be looking for is 11.5.

Any ideas?

Thanks.

John

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)

wrote in message
oups.com...
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(D2:D5000="A",A2:A5000,""))

The answer I'd be looking for is 11.5.

Any ideas?

Thanks.

John

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