Median IF

[Akamy]

Good Afternoon!

I'm having a problem getting the formula right to calculate the median of a column that contains two pieces of data in corresponding cells.

I've attached my excel workbook that I'm working in.

In the "Comparison Data 2" tab I need to calculate the Median in Column E if the Difference (Column A) is located in column M on the Aggregated Raw Data tab.

The median calculation will be calculated from Column F in the aggregated raw data tab if it contains a value from Comparison Data 2 column A in Aggregated Raw Data column M in the same row.

Am I making sense?

The calculation for median should be similar to the calculation for AverageA and AverageB in Comparison Data 2.

Can anyone help?

[daddylonglegs]

There is no actual MEDIANIF function so you need to use MEDIAN function with IFs, as an array formula so for E2 this formula

=IFERROR(IF(Year="ALL",MEDIAN(IF(Difference=A2,yppoTeamA)),MEDIAN(IF(yearColumn=Year,IF(Difference=A2,yppoTeamA)))),0)

confirmed with CTRL+SHIFT+ENTER and copied down

That actually works for all rows except the first because when A2=0 the formula interprets blanks in Difference as zeroes and distorts the result....so for row 2 only (or you can include it in the generic formula to make it work for all instances where the criterion is zero) you need this version with an extra condition

=IFERROR(IF(Year="ALL",MEDIAN(IF(Difference=A2,IF(Difference<>"",yppoTeamA))),MEDIAN(IF(yearColumn=Year,IF(Difference=A2,IF(Difference<>"",yppoTeamA))))),0)

Repeat with relevant changes for MedianB

[Akamy]

Thanks so much! That worked perfectly!