How to get the percentile value of a dynamic range?

[dEX2018]

Hello

I'm struggling to write the correct formula to get the 90th percentile of a dynamic range. I don't want to do it in VBA as it seems to be possible with an Excel formula.

I've found this post but I don't manage to amend the formula properly: http://www.mrexcel.com/forum/excel-questions/712238-how-get-percentile-value-over-dynamic-range.html

Below is a sample of my data set (sorted on column A). I would to have the 90th percentile of B2:B6 (so for 20400) and the 90th percentile of B7:B10 (for 23500). But the formula should dynamically take the correct range, as longs as value in column A matches '20400', '23500', ...

[TABLE="width: 250"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD][/TD]
[TD]90th percentile[/TD]
[/TR]
[TR]
[TD]20400[/TD]
[TD]5,756[/TD]
[TD]20400[/TD]
[TD][/TD] [/TR] [TR] [TD]20400[/TD] [TD]15,326[/TD] [TD]23500[/TD] [TD][formula][/TD] [/TR] [TR] [TD]20400[/TD] [TD]524,365[/TD] [TD][/TD] [TD][/TD] [/TR] [TR] [TD]20400[/TD] [TD]2,750,365[/TD] [TD][/TD] [TD][/TD] [/TR] [TR] [TD]20400[/TD] [TD]956,324[/TD] [TD][/TD] [TD][/TD] [/TR] [TR] [TD]23500[/TD] [TD]75,653[/TD] [TD][/TD] [TD][/TD] [/TR] [TR] [TD]23500[/TD] [TD]654,365[/TD] [TD][/TD] [TD][/TD] [/TR] [TR] [TD]23500[/TD] [TD]15,236[/TD] [TD][/TD] [TD][/TD] [/TR] [TR] [TD]23500[/TD] [TD]236,543[/TD] [TD][/TD] [TD][/TD] [/TR] </tbody>[/TABLE] Many thanks for your help!

 

[yesterdays]

Assume "20400" in cell C2
In D2 Paste this :
=PERCENTILE.INC(LARGE(IF($A$2:$A$10=C2,$B$2:$B$10,0),ROW(INDIRECT(1&":"&COUNTIF($A$2:$A$10,C2)))),0.9)

Ctrl Shift Enter

 

[dEX2018]

Thanks but isn't there an easier way? Can't I combine the Match with the Percentile function?

Something like this (the formula gives an error so I'm trying now to make it work):

In Cell D2 (for 20400):

=PERCENTILE(B2:B & MATCH(C2,A:A,1),0.9)

In Cell E2 (for 23500):

=PERCENTILE(B2:B & MATCH(C3,A:A,1),0.9)

where Match calculates the end of the range.

 

[yesterdays]

I don't think your formula going to work
As you can see the range for calculates for 23500 are from B7:B10 , not B2
And may be just me, but i can't come up with a way to make MATCH return end of the range.

 

[steve the fish]

You could use

=PERCENTILE.INC(IF(A:A=C1,B:B),0.9)

Seems to be quick even using the full column references.

 

[yesterdays]

You could use

=PERCENTILE.INC(IF(A:A=C1,B:B),0.9)

Seems to be quick even using the full column references.

 

[steve the fish]

I don't think your formula going to work
As you can see the range for calculates for 23500 are from B7:B10 , not B2
And may be just me, but i can't come up with a way to make MATCH return end of the range.

This works for numerical data.

=MATCH(9.999999E+307,A:A,1)

 

[yesterdays]

This works for numerical data.

=MATCH(9.999999E+307,A:A,1)

Sure ...
^^` Sr I still can't come up with any thing

 

[dEX2018]

I don't think your formula going to work
As you can see the range for calculates for 23500 are from B7:B10 , not B2
And may be just me, but i can't come up with a way to make MATCH return end of the range.

Enter

=MATCH(C2,A:A,0)

to retrieve beginning row of range (2 in my example) and

=MATCH(C2,A:A,1)

to retrieve end of range (6) .

 

[dEX2018]

You could use

=PERCENTILE.INC(IF(A:A=C1,B:B),0.9)

Seems to be quick even using the full column references.

Steve, this gives the same result for 20400 & 23500 because if true the array to calculate the percentile is always the full column B, not a variable range.

Below formula gives #VALUE error:

=PERCENTILE(IF(A:A=C3,"B" & MATCH(A2,A:A,0) & ":B" & MATCH(A2,A:A,1)),0.9)

I'm really looking for a combination of PERCENTILE and MATCH but no luck so far ...