Converting my "AverageIf" to "MedianIf" for Google sheets

[Dustinkli]

I have a formula which looks at the average of a column based on 2 criteria:

=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:

=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.

 

[shift-del]

=Median(Index(QUERY(Sheet1!$A:$BT,"select H, D where D = '"&$A4&"'",1),,1))

I'm pretty sure that QUERY() is not an Excel function.

 

[Dustinkli]

Oh, You're correct. I'm using GoogleSheets. I tend to think they're interchangeable as far as the functions go but looks like this is one of those cases where it isn't.

 

[Fluff]

In future when asking questions about anything other than Excel you need to make it clear in your title & such questions should be posted in the General Discussion & Other Applications section of the board.

I have done that for you this time.