90th Percentile Calc for Grouped Items

[camarcano]

Hello!

Hope you are doing great. I have a long list with the following structure:

Name Res 90th P Res
A 110.8 113.2
A 115.6
A 105.8
A 106.2
A 104.9
A 102.2
B 114.1 113.44
B 111.9
B 109.8
B 110.8
C 108.5 108.98
C 108
C 109.7
C 108.4
C 102.2
C 101.8
C 96.7

In column "Name" there are a lot of items grouped by its name, under column "Res" there are data points for different events belonging to each item in column "Name". What I need to do is to automatically calculate the 90th percentile for the events in "Res" that belong to each item in "Name". I manually did for the example here, but I had to manually pick each range for each name (A, B and C) which for a long list is impossible to do.

Is there a way to do the calculation for each group without knowing the size of each group (the array) for the PERCENTILE formula?

Thanks in advance for your time and help!

Regards,

Carlos.

 

[AhoyNC]

Maybe one of the options below.
These are array formulas and must be enter with CTRL-SHIFT-ENTER (except in Excel 365 just ENTER).

Book1
	A	B	C	D	E	F
1	Name	Res	90th P Res		Name	90th P Res
2	A	110.8	113.2		A	113.2
3	A	115.6			B	113.44
4	A	105.8			C	108.98
5	A	106.2
6	A	104.9
7	A	102.2
8	B	114.1	113.44
9	B	111.9
10	B	109.8
11	B	110.8
12	C	108.5	108.98
13	C	108
14	C	109.7
15	C	108.4
16	C	102.2
17	C	101.8
18	C	96.7
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=PERCENTILE.INC(IF($A$2:$A$18=E2,$B$2:$B$18),0.9)
C2:C18	C2	=IF(COUNTIF($A$2:$A2,A2)=1,PERCENTILE.INC(IF($A$2:$A$18=A2,$B$2:$B$18),0.9),"")

 

[camarcano]

Maybe one of the options below.
These are array formulas and must be enter with CTRL-SHIFT-ENTER (except in Excel 365 just ENTER).

Book1
	A	B	C	D	E	F
1	Name	Res	90th P Res		Name	90th P Res
2	A	110.8	113.2		A	113.2
3	A	115.6			B	113.44
4	A	105.8			C	108.98
5	A	106.2
6	A	104.9
7	A	102.2
8	B	114.1	113.44
9	B	111.9
10	B	109.8
11	B	110.8
12	C	108.5	108.98
13	C	108
14	C	109.7
15	C	108.4
16	C	102.2
17	C	101.8
18	C	96.7
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=PERCENTILE.INC(IF($A$2:$A$18=E2,$B$2:$B$18),0.9)
C2:C18	C2	=IF(COUNTIF($A$2:$A2,A2)=1,PERCENTILE.INC(IF($A$2:$A$18=A2,$B$2:$B$18),0.9),"")

I went for the first option, and it works beautifully, thanks!