AVERAGEIFS Function

[kipper19]

Would anyone know how to average say the highest 20 numbers out of 200-300 numbers in a AVERAGEIFS statement
I have tried the following but it did not work,
=AVERAGEIFS(E2:E940000,">="&LARGE(E2:E940000,20),A2:A940000,K4,B2:B940000,J4,C2:C940000,I4)<o></o>

 

[Eric W]

Try:

=AVERAGE(LARGE(IF(A2:A940000=K4,IF(B2:B940000=J4,IF(C2:C940000=I4,E2:E940000))),ROW(INDIRECT("1:20"))))

with Control+Shift+Enter.

It's noticeably slow, but that's due to the large range, and still calculates in under a second.

 

[AhoyNC]

The first part of AVERAGEIFS needs to be the "average range", looks like you have this as the first criteria.

=AVERAGEIFS(E2:E940000,">="&LARGE(E2:E940000,20),A2:A940000,K4,B2:B940000,J4,C2:C940000,I4)

Assuming your range to average is E2:E940000 try:

=AVERAGEIFS(E2:E940000,E2:E940000,">="&LARGE(E2:E940000,20),A2:A940000,K4,B2:B940000,J4,C2:C940000,I4)

 

[kipper19]

Eric, thank you very much, really appreciate you help

 

[kipper19]

Ahoy,
Thank you but this version brought up a #DIV/0" error


QUOTE=AhoyNC;5013548]The first part of AVERAGEIFS needs to be the "average range", looks like you have this as the first criteria.

=AVERAGEIFS(E2:E940000,">="&LARGE(E2:E940000,20),A2:A940000,K4,B2:B940000,J4,C2:C940000,I4)

Assuming your range to average is E2:E940000 try:

=AVERAGEIFS(E2:E940000,E2:E940000,">="&LARGE(E2:E940000,20),A2:A940000,K4,B2:B940000,J4,C2:C940000,I4)
[/QUOTE]