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!

 

[steve the fish]

It needs to be array entered. CSE.

 

[yesterdays]

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

1. The array part of PERCENTILE need to be some thing to return array like $B$1:$B$10 or {20400,20400,...}

 

[yesterdays]

To do that, you much use some thing like array formula ( like the one Steve gave you - confirm with Ctrl Shift Enter) or INDIRECT Function
2. Your IF in above formula is wrong, it will return text "B2:B6" for each cell in column A which = C3 ( and your formula need to confirm with Ctrl Shift Enter too)
I'm quite sure that you are not familiar with Array formula
To understand how it works,Paste this in your worksheet
=PERCENTILE.INC(IF(A1:A10=C2,B1:B10),0.9)
confirm with Ctrl Shift Enter
then use Evaluate formula tools to see how it works
Cheer!

 

[dEX2018]

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

Ok, now I've got it to work. Thanks for this formula! The only drawback (compared to the VBA code I have written in the meantime) is that, for a large data set, the calculation in Excel takes a lot of time.

Thanks to yesterdays as well for the info in his last post!