MEDIAN with multiple IF conditions

[Ken Soona]

I need to write a formula that calculates a median value, with several IF clauses. EG 'data' tab is below:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Person[/TD]
[TD]Sales[/TD]
[TD]Margin[/TD]
[/TR]
[TR]
[TD]Tommy[/TD]
[TD]2[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Jimmy[/TD]
[TD]3[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]Henry[/TD]
[TD]5[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]Jimmy[/TD]
[TD]4[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Jimmy[/TD]
[TD]6[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Tommy[/TD]
[TD]10[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Henry[/TD]
[TD]3[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]Henry[/TD]
[TD]5[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]Tommy[/TD]
[TD]7[/TD]
[TD]500[/TD]
[/TR]
</tbody>[/TABLE]

In another tab, I have what I want the result to be, as well as some constraints
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]MinSales[/TD]
[TD]MinMargin[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]p50Sales[/TD]
[TD]p50Margin[/TD]
[/TR]
[TR]
[TD]Tommy[/TD]
[TD](7)[/TD]
[TD](200)[/TD]
[/TR]
[TR]
[TD]Jimmy[/TD]
[TD](4)[/TD]
[TD](200)[/TD]
[/TR]
[TR]
[TD]Henry[/TD]
[TD](5)[/TD]
[TD](700)[/TD]
[/TR]
</tbody>[/TABLE]

I need to write a formula that will calculate median for each person named, with or without constraints on MinSales and MinMargin.

I have something like this so far in cell B5. This should calculate to 7, the median number for sales for Tommy, given a sale minimum of 0 or greater, and a margin minimum of 0 or greater.

{=MEDIAN(IF(AND(data!$A$2:$A$10=A5,data!$B$2:$B$10>=B2,data!!$C$2:$C$10>=C2),data!$A$2:$A$10))}

I have also tried:

{=MEDIAN(IF(data!$A$2:$A$10=A5,IF(data!$B$2:$B$10>=B2,IF(data!!$C$2:$C$10>=C2,data!$A$2:$A$10))))}

In another note...I have gotten something like this to work elsewhere, with two IF instead of three, and both of them are static numbers, rather than dynamic references. That is unhelpful though, I need to make this such that someone else can plug numbers into the min sales and min margin cells to see how the data differ.

Any help is appreciated, thank you!

 

[DRSteele]

The use of function AND is the problem: it's delivering a single TRUE or FALSE. What you need to do is multiply the arrays. Note your sample formula has an extraneous exclamation mark next the the spreadsheet name 'data.'

Put this array formula into B5 with Ctrl+Shift+Enter and then copy across and down.

=MEDIAN(IF((data!$A$2:$A$10=$A5)*(data!$B$2:$B$10>=$B$2)*(data!$C$2:$C$10>=$C$2),data!B$2:B$10))

 

[Ken Soona]

I know the AND part, I was just trying to state that I had tried more than one thing, even if it was wrong

Your solution works perfect, thank you!

 

[DRSteele]

Super. You're welcome.