Calculate average using multiple criteria. (averageifs or something else?)

[VGPOP]

Hello everyone,

I would like to see what formula to use to calculate average of last entries based on two conditions:

For example:

C12 = R
C13 = H
C14 = R
C15 = R
C16 = H
C17 = H
C18 = H
C19 = R
...
...
C173 = R

D12 = 5
D13 = 6
D14 = 0
D15 = 1
D16 = 9
D17 = 0
D18 = 11
D19 = 6
...
...
D173 = 3


J12 = Mark
J13 = Johnson
J14 = Peter
J15 = Bob
J16 = Johnson
J17 = Johnson
J18 = Peter
J19 = Mark
...
...
J173 = Mark

In the first row (row 12) to make it clear, Mark was on the road and had 5 points.

I want a formula that calculate the average of the "last three entries", let's say, "Johnson" had when he was on the road ("R")

I have excel 2007, and I dont know of averageifs will work.

I'd appreciate any help.

Thanks.

 

[barry houdini]

Try this "array formula"

=AVERAGE(IF(C12:C173="R",IF(J12:J173="Johnson",IF(ROW(D12:D173)>=LARGE(IF(C12:C173="R",IF(J12:J173="Johnson",ROW(D12:D173))),3),D12:D173))))

confirmed with CTRL+SHIFT+ENTER

Assumes there will be at least 3 rows that meet the criteria.....if not then you'll get an error....or edit like this to return a message

=IFERROR(AVERAGE(IF(C12:C173="R",IF(J12:J173="Johnson",IF(ROW(D12:D173)>=LARGE(IF(C12:C173="R",IF(J12:J173="Johnson",ROW(D12:D173))),3),D12:D173)))),"Less than 3 matches")

 

[VGPOP]

Try this "array formula"

=AVERAGE(IF(C12:C173="R",IF(J12:J173="Johnson",IF(ROW(D12:D173)>=LARGE(IF(C12:C173="R",IF(J12:J173="Johnson",ROW(D12:D173))),3),D12:D173))))

confirmed with CTRL+SHIFT+ENTER

Assumes there will be at least 3 rows that meet the criteria.....if not then you'll get an error....or edit like this to return a message

=IFERROR(AVERAGE(IF(C12:C173="R",IF(J12:J173="Johnson",IF(ROW(D12:D173)>=LARGE(IF(C12:C173="R",IF(J12:J173="Johnson",ROW(D12:D173))),3),D12:D173)))),"Less than 3 matches")

Beautiful. It does work!

Thanks.

I was trying to use sumproduct, like combined all three requests, but couldnt come up with correct answer.

Thanks again!.