MAX(INDEX(MATCH(LARGE(IF(PERCENTILE array formula... OH MY!!!

[dwcjmilo]

Hey Everyone!

I could use some assistance in getting past a wall I've come upon. I'm needing to calculate the MAX value of a column after scrubbing the top 10% & bottom 10% from a separate staffed column using a Percentile formula.

These two Formulas are pulling the Top & Bottom Staffed person outside of the Top 10% & Bottom 10% staffed as I am wanting:


<PERCENTILE(E6:e16,0.9),e6:e16),1)
<PERCENTILE(E6:e16,0.9),e6:e16),1)
LARGE(IF(E6:E16<<PERCENTILE(E6:e16,0.9),e6:e16),1)
PERCENTILE(E6:E16,0.9),E6:E16),1)
</PERCENTILE(E6:e16,0.9),e6:e16),1)


SMALL(IF(E6:E16><PERCENTILE(E6:e16,0.9),e6:e16),1)
PERCENTILE(E6:E16,0.1),E6:E16),1)</PERCENTILE(E6:e16,0.9),e6:e16),1)



These two array formulas are pulling the MTD Rev % of those two formulas above:


INDEX(C6:C16,MATCH(LARGE(IF(E6:E16<<PERCENTILE(E6:e16,0.9),e6:e16),1),e6:e16,0))
PERCENTILE(E6:E16,0.9),E6:E16),1),E6:E16,0))<PERCENTILE(E6:e16,0.9),e6:e16),1),e6:e16,0))}



INDEX(C6:C16,MATCH(SMALL(IF(E6:E16>PERCENTILE(E6:E16,0.1),E6:E16),1),E6:E16,0))


Here is where I'm stuck... I need to pull all of the MTD Rev % references of each of the people in the middle 80% of the staffed column. Once I can reference each of those dynamically, then I can use a MIN & MAX formula to find the MIN & MAX MTD Rev % for those middle 80% staffed people.

Doing this manually I know the correct MAX MTD Rev % value of the scrubbed staffed people is 73.4% and the MIN is 50.6%

Disclaimer... This is a sample set of data and needs to dynamically calculate from an export of data (approx. 1000 rows) that isn't in the staffed order shown below.

If you have any tips on how I can do this in a single formula, I would be forever grateful! If one cell isn't possible, I "might" be able to use helper cells/columns, but this is highly discouraged.
[TABLE="width: 439"]
<tbody>[TR]
[TD]
MTD Rev %
(Column C)</p>[/TD]
[TD]
MTD Revenue
(Column D)</p>[/TD]
[TD]
Staffed Hours
(Column E)</p>[/TD]
[/TR]
[TR]
[TD]
65.2%</p>[/TD]
[TD]
$358,479.00</p>[/TD]
[TD]
170.57</p>[/TD]
[/TR]
[TR]
[TD]
85.4%</p>[/TD]
[TD]
$469,398.00</p>[/TD]
[TD]
146.74</p>[/TD]
[/TR]
[TR]
[TD]
51.7%</p>[/TD]
[TD]
$284,383.00</p>[/TD]
[TD]
146.48</p>[/TD]
[/TR]
[TR]
[TD]
68.6%</p>[/TD]
[TD]
$377,265.00</p>[/TD]
[TD]
144.32</p>[/TD]
[/TR]
[TR]
[TD]
70.6%</p>[/TD]
[TD]
$387,962.00</p>[/TD]
[TD]
141.08</p>[/TD]
[/TR]
[TR]
[TD]
73.4%</p>[/TD]
[TD]
$403,162.00</p>[/TD]
[TD]
137.77</p>[/TD]
[/TR]
[TR]
[TD]
50.6%</p>[/TD]
[TD]
$277,928.00</p>[/TD]
[TD]
136.37</p>[/TD]
[/TR]
[TR]
[TD]
72.5%</p>[/TD]
[TD]
$398,670.00</p>[/TD]
[TD]
129.75</p>[/TD]
[/TR]
[TR]
[TD]
66.7%</p>[/TD]
[TD]
$366,786.00</p>[/TD]
[TD]
122.84</p>[/TD]
[/TR]
[TR]
[TD]
62.3%</p>[/TD]
[TD]
$342,485.00</p>[/TD]
[TD]
120.72</p>[/TD]
[/TR]
[TR]
[TD]
0.4%</p>[/TD]
[TD]
$2,279.00</p>[/TD]
[TD]
0.00</p>[/TD]
[/TR]
</tbody>[/TABLE]
</PERCENTILE(E6:e16,0.9),e6:e16),1),e6:e16,0))}
</PERCENTILE(E6:e16,0.9),e6:e16),1),e6:e16,0))
</PERCENTILE(E6:e16,0.9),e6:e16),1)
</PERCENTILE(E6:e16,0.9),e6:e16),1)

 

[Aladin Akyurek]

Is this an input sample? If it's, is it complete? And care also the output exhibit?

 

[dwcjmilo]

Is this an input sample? If it's, is it complete? And care also the output exhibit?

Hey Aladin,

Those 3 columns are from a data export from Oracle/Hyperion program. The full export has other data in the A, B columns prior to this data and additional data in the F, G, etc columns. This sample set is 11 rows of data from what would be up to 1000 rows of data. Fortunately, if a formula works for this 11-row set, it would work for the 1000 row set. As far as the output exhibit, I would need to clarify what you mean by this to accurately answer your question.

 

[Aladin Akyurek]

Hey Aladin,

Those 3 columns are from a data export from Oracle/Hyperion program. The full export has other data in the A, B columns prior to this data and additional data in the F, G, etc columns. This sample set is 11 rows of data from what would be up to 1000 rows of data. Fortunately, if a formula works for this 11-row set, it would work for the 1000 row set. As far as the output exhibit, I would need to clarify what you mean by this to accurately answer your question.

You write: I need to pull all of the MTD Rev % references of each of the people in the middle 80% of the staffed column. I don't see any people anywhere in the exhibit you posted. That's the reason why I asked whether this was an example data sample...

 

[dwcjmilo]

You write: I need to pull all of the MTD Rev % references of each of the people in the middle 80% of the staffed column. I don't see any people anywhere in the exhibit you posted. That's the reason why I asked whether this was an example data sample...

Hey Aladin,

The middle 80% of the staffed column would be these people:

[TABLE="class: cms_table, width: 439"]
<tbody>[TR]
[TD]51.7%[/TD]
[TD]$284,383.00[/TD]
[TD]146.48[/TD]
[/TR]
[TR]
[TD]68.6%[/TD]
[TD]$377,265.00[/TD]
[TD]144.32[/TD]
[/TR]
[TR]
[TD]70.6%[/TD]
[TD]$387,962.00[/TD]
[TD]141.08[/TD]
[/TR]
[TR]
[TD]73.4%[/TD]
[TD]$403,162.00[/TD]
[TD]137.77[/TD]
[/TR]
[TR]
[TD]50.6%[/TD]
[TD]$277,928.00[/TD]
[TD]136.37[/TD]
[/TR]
[TR]
[TD]72.5%[/TD]
[TD]$398,670.00[/TD]
[TD]129.75[/TD]
[/TR]
[TR]
[TD]66.7%[/TD]
[TD]$366,786.00[/TD]
[TD]122.84[/TD]
[/TR]
</tbody>[/TABLE]

So I need to find a way to pull that first column for each of those people based on them being the middle 80% of the staffed column. This is entirely doable if the staffed column exported in the order shown and would never be resorted. But the export file Im using is essentially the sample set I listed but a jumbled order in the staffed column.

I'm sorry if Im not explaining myself thoroughly enough... I know this is a difficult request and I'm currently experimenting with INDIRECT functions while waiting on suggestions from the forum here.

 

[dwcjmilo]

You write: I need to pull all of the MTD Rev % references of each of the people in the middle 80% of the staffed column. I don't see any people anywhere in the exhibit you posted. That's the reason why I asked whether this was an example data sample...

Hey Aladin,

I was able to solve this a few mins ago... here are the Array formulas:

MAX(IF(E6:E16<=LARGE(IF(E6:E16<PERCENTILE(E6:E16,0.9),E6:E16),1),C6:C16))

MIN(IF(E6:E16>=SMALL(IF(E6:E16>PERCENTILE(E6:E16,0.1),E6:E16),1),C6:C16))

 

[Aladin Akyurek]

Hey Aladin,

I was able to solve this a few mins ago... here are the Array formulas:

MAX(IF(E6:E16<=LARGE(IF(E6:E16<percentile(e6:e16,0.9),e6:e16),1),c6:c16))

MIN(IF(E6:E16>=SMALL(IF(E6:E16>PERCENTILE(E6:E16,0.1),E6:E16),1),C6:C16))

I intended to come back on this... What follows is how I would do it. Perhaps you did the same way...

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

146.48​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

122.84​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD] MTD Rev %[/TD]
[TD] MTD Revenue[/TD]
[TD] Staffed Hours[/TD]
[TD][/TD]
[TD] MTD Rev %[/TD]
[TD] MTD Revenue[/TD]
[TD] Staffed Hours[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD] 65.20%[/TD]
[TD] $358,479.00 [/TD]
[TD] 170.57[/TD]
[TD][/TD]
[TD] 51.70%[/TD]
[TD] $284,383.00 [/TD]
[TD]

146.48​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD] 85.40%[/TD]
[TD] $469,398.00 [/TD]
[TD] 146.74[/TD]
[TD][/TD]
[TD] 68.60%[/TD]
[TD] $377,265.00 [/TD]
[TD]

144.32​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD] 51.70%[/TD]
[TD] $284,383.00 [/TD]
[TD] 146.48[/TD]
[TD][/TD]
[TD] 70.60%[/TD]
[TD] $387,962.00 [/TD]
[TD]

141.08​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD] 68.60%[/TD]
[TD] $377,265.00 [/TD]
[TD] 144.32[/TD]
[TD][/TD]
[TD] 73.40%[/TD]
[TD] $403,162.00 [/TD]
[TD]

137.77​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD] 70.60%[/TD]
[TD] $387,962.00 [/TD]
[TD] 141.08[/TD]
[TD][/TD]
[TD] 50.60%[/TD]
[TD] $277,928.00 [/TD]
[TD]

136.37​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD] 73.40%[/TD]
[TD] $403,162.00 [/TD]
[TD] 137.77[/TD]
[TD][/TD]
[TD] 72.50%[/TD]
[TD] $398,670.00 [/TD]
[TD]

129.75​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD] 50.60%[/TD]
[TD] $277,928.00 [/TD]
[TD] 136.37[/TD]
[TD][/TD]
[TD] 66.70%[/TD]
[TD] $366,786.00 [/TD]
[TD]

122.84​

[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD] 72.50%[/TD]
[TD] $398,670.00 [/TD]
[TD] 129.75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD] 66.70%[/TD]
[TD] $366,786.00 [/TD]
[TD] 122.84[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD] 62.30%[/TD]
[TD] $342,485.00 [/TD]
[TD] 120.72[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD] 0.40%[/TD]
[TD] $2,279.00 [/TD]
[TD] 0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In I2 control+shift+enter, not just enter:

</percentile(e6:e16,0.9),e6:e16),1),c6:c16))
=MAX(IF(E6:E16<PERCENTILE(E6:E16,0.9),E6:E16))

In I3 control+shift+enter:

=MIN(IF(E6:E16>PERCENTILE(E6:E16,0.1),E6:E16))

In I4 just enter:

=COUNTIFS($E$6:$E$16,"<="&I2,$E$6:$E$16,">="&I3)

In G6 control+shift+enter, copy across to H6, and down:

=IF($I6="","",INDEX(C$6:C$16,SMALL(IF($E$6:$E$16=$I6,ROW(C$6:C$16)-ROW(C$6)+1),COUNTIFS($I$6:$I6,$I6))))

In I6 control+shift+enter and copy down:

=IF(ROWS($I$6:I6)>$I$4,"",LARGE(IF($E$6:$E$16<=$I$2,IF($E$6:$E$16>=$I$3,$E$6:$E$16)),ROWS($I$6:I6)))

Note. If this set up is to heavy to execute, we can speed it up by means of an additional output range.