AVERAGE/MAX/MIN/MEDIAN meet 3 Criteria (2 criteria in Row, 1 in Column)

[lawi227]

The formula I am looking to build will be used to calculate the average, max, min, and median. For this post, I will stick to just calculating the average (but it would be extremely helpful if in the formula I could replace the AVERAGE function with MIN, MAX, or MEDIAN).

I have a table of data located in: 'LPA Database Raw Data'!$F$5:$BG$741

I need to find all the cells in the table above and calculate the average. To first identify which columns the data is located in, there are two criteria that need to be met:

1) criteria range: 'LPA Database Raw Data'!$F$2:$BG$2
criteria: 'LPA Database Scores '!$D$4:$P$4 NOTE: this is a merged cell that contains text

2) criteria range: 'LPA Database Raw Data'!$F$4:$BG$4
criteria: 'LPA Database Scores '!$E$5:$H$5


Lastly, we will have to select the appropriate rows using the following criteria:

3) criteria range: 'LPA Database Raw Data'!$C$5:$C$741
criteria: 'LPA Database Scores '!$D7

I tried to use the below formula, but it did not work. You can at least see the criteria and criteria range in the formula. Note: I would ideally like to avoid AVERAGEIFS since this formula only works in 2016.

=AVERAGEIFS('LPA Database Raw Data'!$F$5:$BG$741,'LPA Database Raw Data'!$F$4:$BG$4,'LPA Database Scores '!$E$5:$H$5,'LPA Database Raw Data'!$F$2:$BG$2,'LPA Database Scores '!$D$4:$P$4,'LPA Database Raw Data'!$C$5:$C$741,'LPA Database Scores '!$D7)

I'm happy to provide any more information necessary.

Thanks so much! This is a pretty advanced formula in my opinion, so I appreciate learning new approaches.

 

[lawi227]

BUMP. Looking for help still...

 

[barry houdini]

Try this array formula

=AVERAGE(IF(ISNUMBER(MATCH('LPA Database Raw Data'!$F$4:$BG$4,'LPA Database Scores '!$E$5:$H$5,0)),IF(ISNUMBER(MATCH('LPA Database Raw Data'!$F$2:$BG$2,'LPA Database Scores '!$D$4:$P$4,0)),IF('LPA Database Raw Data'!$C$5:$C$741='LPA Database Scores '!$D7,'LPA Database Raw Data'!$F$5:$BG$741))))

confirm with CTRL+SHIFT+ENTER

You can use the same formula for MIN, MAX or MEDIAN, just change the function name