INDEX, MATCH, AVERAGE Formula

[kipper19]

Looking for some help

I have a spreadsheet which has around 20K rows of data, Im looking for a formula that I enter 3 parameters into a search box and it will calculate the average of numbers it finds against the search parameters in column D,
Column D could have up to 50 different numbers against the same parameters so Im looking to average them,
I have attached a screen shot of what Im trying to achieve, hope someone could shed some light

 

[cunningAce]

Give this a try

=AVERAGEIFS(D2:D15,A2:A15,G2,B2:B15,G3,C2:C15,G4)

Where G2,G3 & G4 are your selections

 

[kipper19]

CunningAce

I really appreciate your help, works well, thank you

 

[cunningAce]

No problem

 

[kipper19]

CunningAce, one question, as this formula is averaging lots of numbers that meet the search criteria, it could be 20 up to 200, how could I average say the highest top 10 or 20 numbers, I did search online and tried this but it did not work =AVERAGEIFS(E2:E940000,">="&LARGE(E2:E940000,20),A2:A940000,K4,B2:B940000,J4,C2:C940000,I4)<o></o>

 

[cunningAce]

Hi,

This is an array formula so you will need to confirm using CTRL, SHIFT & ENTER

=AVERAGE(LARGE(IF((A2:A62=G2)*(B2:B62=G3)*(C2:C62=G4),D2:D62),{1,2,3,4,5,6,7,8,9,10}))

You can expand the red section to choose how many scores to evaluate as part of the average

 

[kipper19]

Really appreciate your help, thank you kindly