Dynamically calculate range within PERCENTILE.INC() formula

[Mihos]

I have a large data set where the data is delineated by a field called "Category." For each Category, I'd like to calculate the PERCENTILE.INC for each row within that Category. For example, here is some sample data with the (manual) formula. I'd like a way to automatically calculate the range variable because I need to run PERCENTILE.INC for many hundreds of Categories and each one has a different range length. (I'm on Windows 7, Excel 2010)

	A	B	C	D
1	Category	ID	Impact	CALC
2	ACOUSTICS	01	0.365	=PERCENTILE.INC(C2:C6,[@[Impact]])
3	ACOUSTICS	02	0.721	=PERCENTILE.INC(C2:C6,[@[Impact]])
4	ACOUSTICS	03	1.022	=PERCENTILE.INC(C2:C6,[@[Impact]])
5	ACOUSTICS	04	0.912	=PERCENTILE.INC(C2:C6,[@[Impact]])
6	ACOUSTICS	05	0.393	=PERCENTILE.INC(C2:C6,[@[Impact]])
7	AGRONOMY	06	1.227	=PERCENTILE.INC(C7:C9,[@[Impact]])
8	AGRONOMY	07	2.001	=PERCENTILE.INC(C7:C9,[@[Impact]])
9	AGRONOMY	08	0.991	=PERCENTILE.INC(C7:C9,[@[Impact]])

<tbody>
</tbody>

 

[Mihos]

Apologies for the title but I think "array" is probably more appropriate than "range" for what I'm trying to do. Hopefully you get the point though.

 

[thatoneguy650]

Something like this?

Excel Workbook
	A	B	C	D
1	Category	ID	Impact	CALC
2	ACOUSTICS	1	0.365	0.978
3	ACOUSTICS	2	0.721	0.978
4	ACOUSTICS	3	1.022	0.978
5	ACOUSTICS	4	0.912	0.978
6	ACOUSTICS	5	0.393	0.978
7	AGRONOMY	6	1.227	1.8462
8	AGRONOMY	7	2.001	1.8462
9	AGRONOMY	8	0.991	1.8462
Sheet1

 

[Mihos]

Oh man, that is soooo close and I think the reason is because I mistyped and wrote PERCENTILE.INC instead of PERCENTRANK.INC in the original post. So my actual formula was =PERCENTRANK.INC(C2:C6,[@[Impact]]). It also looks like the formula is calculating for each Category though, while I want it to calculate for each row within each category. So, here's what the results should look like in my example (if it's calculating the PercentRank.inc for each row within each Category):

	A	B	C	D
1	Category	ID	Impact	CALC
2	ACOUSTICS	1	0.365	0
3	ACOUSTICS	2	0.721	0.5
4	ACOUSTICS	3	1.022	1
5	ACOUSTICS	4	0.912	0.75
6	ACOUSTICS	5	0.393	0.25
7	AGRONOMY	6	1.227	0.5
8	AGRONOMY	7	2.001	1
9	AGRONOMY	8	0.991	0

<colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 82px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

I'm really sorry for using the wrong function in my original post. It could have been avoided had I written in the actual values I was expecting rather than just the formula.

Something like this?

Sheet1

	A	B	C	D
1	Category	ID	Impact	CALC
2	ACOUSTICS	1	0.365	0.978
3	ACOUSTICS	2	0.721	0.978
4	ACOUSTICS	3	1.022	0.978
5	ACOUSTICS	4	0.912	0.978
6	ACOUSTICS	5	0.393	0.978
7	AGRONOMY	6	1.227	1.8462
8	AGRONOMY	7	2.001	1.8462
9	AGRONOMY	8	0.991	1.8462

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:82px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D2 {=PERCENTILE.INC(IF($A$2:$A$9=A2,C$2:$C$9,""),0.9)} D3 {=PERCENTILE.INC(IF($A$2:$A$9=A3,C$2:$C$9,""),0.9)} D4 {=PERCENTILE.INC(IF($A$2:$A$9=A4,C$2:$C$9,""),0.9)} D5 {=PERCENTILE.INC(IF($A$2:$A$9=A5,C$2:$C$9,""),0.9)} D6 {=PERCENTILE.INC(IF($A$2:$A$9=A6,C$2:$C$9,""),0.9)} D7 {=PERCENTILE.INC(IF($A$2:$A$9=A7,C$2:$C$9,""),0.9)} D8 {=PERCENTILE.INC(IF($A$2:$A$9=A8,C$2:$C$9,""),0.9)} D9 {=PERCENTILE.INC(IF($A$2:$A$9=A9,C$2:$C$9,""),0.9)} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Mihos]

Aha! This formula works though!

=PERCENTRANK.INC(IF([Journal Category]=[@[Journal Category]],[Impact Factor],""),[@[Impact Factor]])

Just a slight modification from your original code. Thank you for the help, thatoneguy650!