How to get the percentile value over a dynamic range

[Arjun Kamath]

Hello,
I have to run the 95th percentile value against a raw data dump from a monitoring tool. The data has multiple devices and each device has about a couple of thousands of samples/values. These values are dynamic, it can be 1000 samples for device 1 and may be 1300 for device 2. The data between 2 devices are separated by an empty row. I need help to run a macro using the percentile formula that can accept a dynamic range in the array so that it should not matter how many samples/values each device has, it should provide the 95 Percentile value.
Current Option: =Percentile(array,value)
What I'm looking for is: =Percentile(A dynamic range starting from a designated cell to all the way till the last cell in that range i.e before the empty row that separate the two devices,value)

Thanks,
Arjun Kamath.

 

[Andrew Poulsom]

Welcome to MrExcel.

Examples for column A:

=PERCENTILE(A1:INDEX(A:A,MATCH(TRUE,INDEX(A:A="",),FALSE)-1),0.95)
=PERCENTILE(INDEX(A:A,MATCH(TRUE,INDEX(A:A="",),FALSE)+1):INDEX(A:A,MATCH(9.99999999999999E+307,A:A)),0.95)

 

[Arjun Kamath]

Welcome to MrExcel.

Examples for column A:

=PERCENTILE(A1:INDEX(A:A,MATCH(TRUE,INDEX(A:A="",),FALSE)-1),0.95)
=PERCENTILE(INDEX(A:A,MATCH(TRUE,INDEX(A:A="",),FALSE)+1):INDEX(A:A,MATCH(9.99999999999999E+307,A:A)),0.95)

Hello Andrew,

Thanks for the quick help. I have tried both the formulla's and the output is as follows:

=PERCENTILE(A1:INDEX(A:A,MATCH(TRUE,INDEX(A:A="",),FALSE)-1),0.95) => #NUM!
=PERCENTILE(INDEX(A:A,MATCH(TRUE,INDEX(A:A="",),FALSE)+1):INDEX(A:A,MATCH(9.99999999999999E+307,A:A)),0.95)[/QUOTE]=> The output is same as =percentile(A:A,0.95). I have Multiple ranges with different number of rows in each and I need the 95th Percentile value of each range. The ranges are seperated by an empty cell.

I'm not able to attach my spreadsheet in forum, therefore providing a typical senario:

ColumnA ColumnB
Device1
1/1/2013 3
2/1/2013 2
3/1/2013 3
4/1/2013 4

Device2
1/1/2013 6
2/1/2013 7
3/1/2013 9
4/1/2013 5
5/1/2013 11
6/1/2013 45
7/1/2013 5

Device3
1/1/2013 66
2/1/2013 77

I need the 95th Percentile value for each of the range mentioned in the cell next to the device name.

Thanks,
Arjun Kamath.

 

[Andrew Poulsom]

Example:

Excel 2010
	A	B	C	D
1	Device1		Device1	3.85
2	01/01/2013	3	Device2	34.8
3	02/01/2013	2	Device3	76.45
4	03/01/2013	3
5	04/01/2013	4
6
7	Device2
8	01/01/2013	6
9	02/01/2013	7
10	03/01/2013	9
11	04/01/2013	5
12	05/01/2013	11
13	06/01/2013	45
14	07/01/2013	5
15
16	Device3
17	01/01/2013	66
18	02/01/2013	77
Sheet2

Cell Formulas
Range	Formula
D1	=PERCENTILE(INDEX(B:B,MATCH(C1,A:A,FALSE)+1):INDEX(INDEX(B:B,MATCH(C1,A:A,FALSE)):B$1000,MATCH(TRUE,INDEX(INDEX(A:A,MATCH(C1,A:A,FALSE)):A$1000="",),FALSE)-1),0.95)
D2	=PERCENTILE(INDEX(B:B,MATCH(C2,A:A,FALSE)+1):INDEX(INDEX(B:B,MATCH(C2,A:A,FALSE)):B$1000,MATCH(TRUE,INDEX(INDEX(A:A,MATCH(C2,A:A,FALSE)):A$1000="",),FALSE)-1),0.95)
D3	=PERCENTILE(INDEX(B:B,MATCH(C3,A:A,FALSE)+1):INDEX(INDEX(B:B,MATCH(C3,A:A,FALSE)):B$1000,MATCH(TRUE,INDEX(INDEX(A:A,MATCH(C3,A:A,FALSE)):A$1000="",),FALSE)-1),0.95)

 

[Josh123]

Hi Andrew,

I have a similar question as the original post:

I need to find the average base salary distinguished by [Company ID] AND [Position ID]. For example, I need to find what the average base salary is for:
Company ID 1, Position A = $95,000 (($90,000 + $95,000 + $100,000)/3)
Company ID 2, Position A = $105,000 (($100,000 + $105,000 + $110,000)/3)
Company ID 1, Position B = $45,000 (($40,000 + $45,000 + $50,000)/3)
Company ID 2, Position B = $55,000 (($50,000 + $55,000 + $60,000)/3)

I have tried the following formula, but it is limited to only one variable:
{=AVERAGE(IF(Table[Position ID]=A,Table[Base Salary],""))}
{=AVERAGE(IF(Table[Company ID]=1,Table[Base Salary],""))}

I thought I might be able to simply include the "AND(" function to include both variables, but I get an error:
{=AVERAGE(IF(AND(Table[Position ID]=A,Table[Company ID]=1),Table[Base Salary],""))}

Thank you.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][Company ID][/TD]
[TD][Position ID][/TD]
[TD][Base Salary][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]$90,000[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]$95,000[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]$100,000[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]B[/TD]
[TD]$40,000[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]B[/TD]
[TD]$45,000[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]B[/TD]
[TD]$50,000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]$100,000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]$105,000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]$110,000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD]$50,000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD]$55,000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD]$60,000[/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

You can't use AND because it returns a single value rather than an array of values. Here are 3 possible formulas:

Excel 2010
	A	B	C	D	E
1	Company ID	Position ID	Base Salary		95,000
2	1	A	90,000		95,000
3	1	A	95,000		95,000
4	1	A	100,000
5	1	B	40,000
6	1	B	45,000
7	1	B	50,000
8	2	A	100,000
9	2	A	105,000
10	2	A	110,000
11	2	B	50,000
12	2	B	55,000
13	2	B	60,000
Sheet4

Cell Formulas
Range	Formula
E3	=AVERAGEIFS(Table[Base Salary],Table[Company ID],1,Table[Position ID],"A")
E1	{=AVERAGE(IF(Table[Company ID]=1,IF(Table[Position ID]="A",Table[Base Salary])))}
E2	{=AVERAGE(IF((Table[Company ID]=1)*(Table[Position ID]="A"),Table[Base Salary]))}
Press CTRL+SHIFT+ENTER to enter array formulas.