ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Percentile Analysis Across 2 Columns (https://www.excelbanter.com/excel-worksheet-functions/201300-percentile-analysis-across-2-columns.html)

BRob
Percentile Analysis Across 2 Columns
 
I'm wanting to calculate decile (or equivalent percentile) salaries to see
if there is any difference between 2 departments (A and B).

So dummy data looks like :

Dept Salary

A £52,670
A £53,530
A £56,117
B £41,437
A £51,809
B £53,530
A £32,803
B £52,670
B £35,398
A £52,670
B £31,077
A £35,398
A £23,831
B £17,100
B £15,929
A £19,602
A £16,688
B £16,307
B £16,688
B £14,065


Range names, which cover about 2000 rows of actual data are used "Dept" and
"Salary"

Can SKS help out

Thanks,


Rob

Mike H
Percentile Analysis Across 2 Columns
 
Hi,

You could use 2 helper columns to seperate Dep'ts A & B

In column C
=IF(A1="A",B1,"")
and in column D
=IF(A1="B",B1,"")

The use percentile on these 2 columns
=PERCENTILE(C1:C20,0.1)
=PERCENTILE(D1:D20,0.1)

Mike

Mike


"BRob" wrote:

I'm wanting to calculate decile (or equivalent percentile) salaries to see
if there is any difference between 2 departments (A and B).

So dummy data looks like :

Dept Salary

A £52,670
A £53,530
A £56,117
B £41,437
A £51,809
B £53,530
A £32,803
B £52,670
B £35,398
A £52,670
B £31,077
A £35,398
A £23,831
B £17,100
B £15,929
A £19,602
A £16,688
B £16,307
B £16,688
B £14,065


Range names, which cover about 2000 rows of actual data are used "Dept" and
"Salary"

Can SKS help out

Thanks,


Rob

BRob
Percentile Analysis Across 2 Columns
 
Tx but I'd simplified the example a bit too much :(

There are about 60 different depts on the live data so, to keep things
manageable, I really need to get it done in a single step.




"Mike H" wrote in message
...
Hi,

You could use 2 helper columns to seperate Dep'ts A & B

In column C
=IF(A1="A",B1,"")
and in column D
=IF(A1="B",B1,"")

The use percentile on these 2 columns
=PERCENTILE(C1:C20,0.1)
=PERCENTILE(D1:D20,0.1)

Mike

Mike


"BRob" wrote:

I'm wanting to calculate decile (or equivalent percentile) salaries to
see
if there is any difference between 2 departments (A and B).

So dummy data looks like :

Dept Salary

A £52,670
A £53,530
A £56,117
B £41,437
A £51,809
B £53,530
A £32,803
B £52,670
B £35,398
A £52,670
B £31,077
A £35,398
A £23,831
B £17,100
B £15,929
A £19,602
A £16,688
B £16,307
B £16,688
B £14,065


Range names, which cover about 2000 rows of actual data are used "Dept"
and
"Salary"

Can SKS help out

Thanks,


Rob

Mike H
Percentile Analysis Across 2 Columns
 
Hi,

To do it in the same column requires an array formula. I've used the
department name "A" in this formula but you could create a list of department
in a column allowing you to drag the formula down. The example formula gives
the 10th percentile so for the 20th change to 0.2

=PERCENTILE(IF(A1:A20="A",B1:B20,FALSE),0.01)

Remember this is an array so commit with CTRL+Shift+Enter and Excel will put
curly brackets around it {} if you do it correctly

Mike

"BRob" wrote:

Tx but I'd simplified the example a bit too much :(

There are about 60 different depts on the live data so, to keep things
manageable, I really need to get it done in a single step.




"Mike H" wrote in message
...
Hi,

You could use 2 helper columns to seperate Dep'ts A & B

In column C
=IF(A1="A",B1,"")
and in column D
=IF(A1="B",B1,"")

The use percentile on these 2 columns
=PERCENTILE(C1:C20,0.1)
=PERCENTILE(D1:D20,0.1)

Mike

Mike


"BRob" wrote:

I'm wanting to calculate decile (or equivalent percentile) salaries to
see
if there is any difference between 2 departments (A and B).

So dummy data looks like :

Dept Salary

A £52,670
A £53,530
A £56,117
B £41,437
A £51,809
B £53,530
A £32,803
B £52,670
B £35,398
A £52,670
B £31,077
A £35,398
A £23,831
B £17,100
B £15,929
A £19,602
A £16,688
B £16,307
B £16,688
B £14,065


Range names, which cover about 2000 rows of actual data are used "Dept"
and
"Salary"

Can SKS help out

Thanks,


Rob

Mike H
Percentile Analysis Across 2 Columns
 
Typo,

For the 10th percentile it's 0.1 not 0.01 as in the formula

"Mike H" wrote:

Hi,

To do it in the same column requires an array formula. I've used the
department name "A" in this formula but you could create a list of department
in a column allowing you to drag the formula down. The example formula gives
the 10th percentile so for the 20th change to 0.2

=PERCENTILE(IF(A1:A20="A",B1:B20,FALSE),0.01)

Remember this is an array so commit with CTRL+Shift+Enter and Excel will put
curly brackets around it {} if you do it correctly

Mike

"BRob" wrote:

Tx but I'd simplified the example a bit too much :(

There are about 60 different depts on the live data so, to keep things
manageable, I really need to get it done in a single step.




"Mike H" wrote in message
...
Hi,

You could use 2 helper columns to seperate Dep'ts A & B

In column C
=IF(A1="A",B1,"")
and in column D
=IF(A1="B",B1,"")

The use percentile on these 2 columns
=PERCENTILE(C1:C20,0.1)
=PERCENTILE(D1:D20,0.1)

Mike

Mike


"BRob" wrote:

I'm wanting to calculate decile (or equivalent percentile) salaries to
see
if there is any difference between 2 departments (A and B).

So dummy data looks like :

Dept Salary

A £52,670
A £53,530
A £56,117
B £41,437
A £51,809
B £53,530
A £32,803
B £52,670
B £35,398
A £52,670
B £31,077
A £35,398
A £23,831
B £17,100
B £15,929
A £19,602
A £16,688
B £16,307
B £16,688
B £14,065


Range names, which cover about 2000 rows of actual data are used "Dept"
and
"Salary"

Can SKS help out

Thanks,


Rob

BRob
Percentile Analysis Across 2 Columns
 
Thanks Mike - I got it working first time <G


"Mike H" wrote in message
...
Typo,

For the 10th percentile it's 0.1 not 0.01 as in the formula

"Mike H" wrote:

Hi,

To do it in the same column requires an array formula. I've used the
department name "A" in this formula but you could create a list of
department
in a column allowing you to drag the formula down. The example formula
gives
the 10th percentile so for the 20th change to 0.2

=PERCENTILE(IF(A1:A20="A",B1:B20,FALSE),0.01)

Remember this is an array so commit with CTRL+Shift+Enter and Excel will
put
curly brackets around it {} if you do it correctly

Mike

"BRob" wrote:

Tx but I'd simplified the example a bit too much :(

There are about 60 different depts on the live data so, to keep things
manageable, I really need to get it done in a single step.




"Mike H" wrote in message
...
Hi,

You could use 2 helper columns to seperate Dep'ts A & B

In column C
=IF(A1="A",B1,"")
and in column D
=IF(A1="B",B1,"")

The use percentile on these 2 columns
=PERCENTILE(C1:C20,0.1)
=PERCENTILE(D1:D20,0.1)

Mike

Mike


"BRob" wrote:

I'm wanting to calculate decile (or equivalent percentile) salaries
to
see
if there is any difference between 2 departments (A and B).

So dummy data looks like :

Dept Salary

A £52,670
A £53,530
A £56,117
B £41,437
A £51,809
B £53,530
A £32,803
B £52,670
B £35,398
A £52,670
B £31,077
A £35,398
A £23,831
B £17,100
B £15,929
A £19,602
A £16,688
B £16,307
B £16,688
B £14,065


Range names, which cover about 2000 rows of actual data are used
"Dept"
and
"Salary"

Can SKS help out

Thanks,


Rob


All times are GMT +1. The time now is 09:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com