|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
September 4th, 2008, 01:02 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 
|
|
#2
September 4th, 2008, 02:03 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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  20,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
|
|
#3
September 4th, 2008, 02:36 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 20,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
|
|
#4
September 4th, 2008, 02:59 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 20,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
|
|
#5
September 4th, 2008, 03:13 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 20,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
|
|
#6
September 4th, 2008, 03:59 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 20,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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
