I have a formula which looks at the average of a column based on 2 criteria:
which looks at 2 variables and then looks up the matching column and rows on another sheet to get the average.
I would like to instead get the Median because there are occasionally deviations that are so huge they throw the average off and make it useless.
I have the following formula working, but it's very very clunky:
I'd like to either have a formula that works like the first one but pulls the median of the range, or have some way to remove the deviations that throw the average off.
Excel Formula:
=IFERROR(AVERAGEIF(Sheet1!$D:$D,$A4,INDEX(Sheet1!$A:$BR,0,MATCH(E$1,Sheet1!$A$1:$BR$1,0))))
which looks at 2 variables and then looks up the matching column and rows on another sheet to get the average.
I would like to instead get the Median because there are occasionally deviations that are so huge they throw the average off and make it useless.
I have the following formula working, but it's very very clunky:
Excel Formula:
=Median(Index(QUERY(Sheet1!$A:$BT,"select H, D where D = '"&$A4&"'",1),,1))
I'd like to either have a formula that works like the first one but pulls the median of the range, or have some way to remove the deviations that throw the average off.