Percentile for specific states
 

Hi

I have included a sample table.

State Priority Age P90 for A____ P90 for B___ P90 for C___
A 1 3
A 1 128
B 2 131
A 3 100
C 2 100
B 3 96
C 3 88
B 3 41
C 1 5
C 2 79

In order to find the P90 for all items in state "A" (column 1) I filter for
A and then copy paste the date in column 3 (Age) into another sheet and find
the percentile. I would like to know if there is a way to find the P90 for
all the states without having to manually do each state one at a time.

Sam

Percentile for specific states
 

You could try something like this ..
Source cols A to C as posted, data from row2 down
List the states in D1 across, ie: A, B, C ..
Put in D2, array-enter the formula by pressing CTRL+SHIFT+ENTER,
instead of just pressing ENTER:
=PERCENTILE(IF($A$2:$A$100=D$1,$C$2:$C$100),0.9)
Copy D2 across as far as required. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sam B" wrote:
Hi

I have included a sample table.

State Priority Age P90 for A____ P90 for B___ P90 for C___
A 1 3
A 1 128
B 2 131
A 3 100
C 2 100
B 3 96
C 3 88
B 3 41
C 1 5
C 2 79

In order to find the P90 for all items in state "A" (column 1) I filter for
A and then copy paste the date in column 3 (Age) into another sheet and find
the percentile. I would like to know if there is a way to find the P90 for
all the states without having to manually do each state one at a time.

Sam

Percentile for specific states
 

Max - Thank you. You helped me get through the first step. I have one more
variation of this.

I want the percentile (P90) for items with State "A" (coulmn 1) and Priority
"1" (column 2). Based on you initial idea I wrote this formula

=PERCENTILE(IF(AND($A$2:$A$100=D$1,$B$2:$B$100="1" ),$C$2:$C$100),0.9)

I array-entered the formula using SHIFT+CTRL+ENTER.

I always get a 0 instead of the actual value. What am I doing wrong? Is it
possible to do this. Can I extend this same principle to check for three
variables (three columns)?

Thank you,


"Max" wrote:

You could try something like this ..
Source cols A to C as posted, data from row2 down
List the states in D1 across, ie: A, B, C ..
Put in D2, array-enter the formula by pressing CTRL+SHIFT+ENTER,
instead of just pressing ENTER:
=PERCENTILE(IF($A$2:$A$100=D$1,$C$2:$C$100),0.9)
Copy D2 across as far as required. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sam B" wrote:
Hi

I have included a sample table.

State Priority Age P90 for A____ P90 for B___ P90 for C___
A 1 3
A 1 128
B 2 131
A 3 100
C 2 100
B 3 96
C 3 88
B 3 41
C 1 5
C 2 79

In order to find the P90 for all items in state "A" (column 1) I filter for
A and then copy paste the date in column 3 (Age) into another sheet and find
the percentile. I would like to know if there is a way to find the P90 for
all the states without having to manually do each state one at a time.

Sam

Percentile for specific states
 

I want the percentile (P90) for items with State "A" (coulmn 1)
and Priority "1" (column 2).

You could extend it in this way,
Array-entered in say, D3, then copied across:
=PERCENTILE(IF(($A$2:$A$100=D$1)*($B$2:$B$100=1),$ C$2:$C$100),0.9)

Note that as the priorities in col B are numbers,
the criteria term for col B should be: $B$2:$B$100=1 (not "1", which is
text)

Can I extend this same principle to check for three variables (three
columns)?

Yes, just extend in the same manner
Eg supposing you have another criteria for col D to be added-on,
where col D = X (say),
then the array expression will look like this, indicatively:
=PERCENTILE(IF((ColA=D$1)*(ColB=1)*(ColD="X"),ColC ),0.9)
Adapt to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sam B" wrote in message
...
Max - Thank you. You helped me get through the first step. I have one more
variation of this.

I want the percentile (P90) for items with State "A" (coulmn 1) and
Priority
"1" (column 2). Based on you initial idea I wrote this formula

=PERCENTILE(IF(AND($A$2:$A$100=D$1,$B$2:$B$100="1" ),$C$2:$C$100),0.9)

I array-entered the formula using SHIFT+CTRL+ENTER.

I always get a 0 instead of the actual value. What am I doing wrong? Is it
possible to do this. Can I extend this same principle to check for three
variables (three columns)?

Thank you,

Percentile for specific states
 

IT WORKS. Thank you. You saved me a lot of time.

Sam B

"Max" wrote:

I want the percentile (P90) for items with State "A" (coulmn 1)
and Priority "1" (column 2).

You could extend it in this way,
Array-entered in say, D3, then copied across:
=PERCENTILE(IF(($A$2:$A$100=D$1)*($B$2:$B$100=1),$ C$2:$C$100),0.9)

Note that as the priorities in col B are numbers,
the criteria term for col B should be: $B$2:$B$100=1 (not "1", which is
text)

Can I extend this same principle to check for three variables (three
columns)?

Yes, just extend in the same manner
Eg supposing you have another criteria for col D to be added-on,
where col D = X (say),
then the array expression will look like this, indicatively:
=PERCENTILE(IF((ColA=D$1)*(ColB=1)*(ColD="X"),ColC ),0.9)
Adapt to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sam B" wrote in message
...
Max - Thank you. You helped me get through the first step. I have one more
variation of this.

I want the percentile (P90) for items with State "A" (coulmn 1) and
Priority
"1" (column 2). Based on you initial idea I wrote this formula

=PERCENTILE(IF(AND($A$2:$A$100=D$1,$B$2:$B$100="1" ),$C$2:$C$100),0.9)

I array-entered the formula using SHIFT+CTRL+ENTER.

I always get a 0 instead of the actual value. What am I doing wrong? Is it
possible to do this. Can I extend this same principle to check for three
variables (three columns)?

Thank you,

Percentile for specific states
 

Welcome, Sam. Glad it helped.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sam B" wrote in message
...
IT WORKS. Thank you. You saved me a lot of time.

Sam B