Average(ifs) meeting certain criteria

=SUM(INDEX(((($B$7:$B$25=$B$3)*($C$7:$C$25=$C$3)+($B$7:$B$25=$C$3)*($C$7:$C$25=$B$3))>0)*($F$7:$F$25),0))/SUM(INDEX(((($B$7:$B$25=$B$3)*($C$7:$C$25=$C$3)+($B$7:$B$25=$C$3)*($C$7:$C$25=$B$3))>0)*(1),0))
Try this formula

See attached. The formula is fairly compact, but is an array formula so if you change it you will need to press Ctrl + Shift + Enter to compute.

You can change the number of largest by adding/subtracting items from the final array of numbers {1,2,3}.

For your first question which I didnt see at first you could also use
=AVERAGE(IF(($B$3&$C$3=$B$7:$B$25&$C$7:$C$25)+($C$3&$B$3=$B$7:$B$25&$C$7:$C$25),$F$7:$F$25))

Which is also an array formula so needs Ctrl + Shift + Enter

Thank's Hawkeye16 !

Siva's formula work how i want. But i need that it that calculate only for last 3 value (for exemple).
I think it must make this index only for the last 3 values ???

$B$7:$B$25=$B$3)*($C$7:$C$25=$C$3)

Like in this post:

http://www.excelforum.com/excel-gene...-criteria.html

But with more criteria

Thanks,

To get an average for the criteria satisfied in either column you can use this "array formula"

=AVERAGE(IF((B7:B25=B3)*(C7:C25=C3)+(B7:B25=C3)*(C7:C25=B3),F7:F25))

confirmed with CTRL+SHIFT+ENTER

and for only the last 3 satisfying those criteria (assuming there will always be at least 3) you can use this array formula

=AVERAGE(IF(ROW(F7:F25)>=LARGE(IF((B7:B25=B3)*(C7:C25=C3)+(B7:B25=C3)*(C7:C25=B3),ROW(F7:F25)),3),IF((B7:B25=B3)*(C7:C25=C3)+(B7:B25=C3)*(C7:C25=B3),F7:F25)))

change the bolded 3 for last n

(self-deleted)

Mr Hawkeye16 your formula will not work if we remove any one of last 3 rows

=SUM(INDEX((ROW($F$7:$F$24)>=LARGE(INDEX(((($B$7:$B$24=$B$3)*($C$7:$C$24=$C$3)+($B$7:$B$24=$C$3)*($C$7:$C$24=$B$3))>0)*ROW($F$7:$F$24),0),3))*($F$7:$F$24),0))/MIN(SUM(INDEX((ROW($F$7:$F$24)>=LARGE(INDEX(((($B$7:$B$24=$B$3)*($C$7:$C$24=$C$3)+($B$7:$B$24=$C$3)*($C$7:$C$24=$B$3))>0)*ROW($F$7:$F$24),0),3))*1,0)),3)
This one also will work

Ah, the last 3. I for some reason thought you meant the largest 3, which is what my attachment answered.

DaddyLongLegs - your formula actually can shorten to this

=AVERAGE(IF(ROW(F7:F25)>=LARGE(IF((B7:B25=B3)*(C7:C25=C3)+(B7:B25=C3)*(C7:C25=B3),ROW(F7:F25)),3),F7:F25))

Originally Posted by Hawkeye16

DaddyLongLegs - your formula actually can shorten to this

=AVERAGE(IF(ROW(F7:F25)>=LARGE(IF((B7:B25=B3)*(C7:C25=C3)+(B7:B25=C3)*(C7:C25=B3),ROW(F7:F25)),3),F7:F25))

No, I don't think so

That works for the specific example given, but what if you change last 3 to last 4? Your version will include rows that don't meet the criteria and you'd get a result of 1.53 rather than the required 5.75

=SUM(INDEX(((($B$7:$B$25=$B$3)*($C$7:$C$25=$C$3)+($B$7:$B$25=$C$3)*($C$7:$C$25=$B$3))>0)*(ROW($F$7:$F$25)>=LARGE(INDEX(((($B$7:$B$25=$B$3)*($C$7:$C$25=$C$3)+($B$7:$B$25=$C$3)*($C$7:$C$25=$B$3))>0)*ROW($F$7:$F$25),0),3))*(F7:F25),0))/MIN(SUM(INDEX(((($B$7:$B$25=$B$3)*($C$7:$C$25=$C$3)+($B$7:$B$25=$C$3)*($C$7:$C$25=$B$3))>0)*(ROW($F$7:$F$25)>=LARGE(INDEX(((($B$7:$B$25=$B$3)*($C$7:$C$25=$C$3)+($B$7:$B$25=$C$3)*($C$7:$C$25=$B$3))>0)*ROW($F$7:$F$25),0),3))*1,0)),3)
Try this formula

I see, doesn't work if the number is greater than the actual matches it finds. I stand corrected.