| Home |
| Search |
| Today's Posts |
|
#1
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
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: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 |
|
#3
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: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 |
|
#4
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: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 |
|
#5
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: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 |
|
#6
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: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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct and Percentile | Excel Worksheet Functions | |||
| to get its k-value from percentile and array | Excel Worksheet Functions | |||
| Conditional Percentile | Excel Discussion (Misc queries) | |||
| Analysis ToolPak installed but no Data Analysis option | Excel Discussion (Misc queries) | |||
| percentile | Excel Worksheet Functions | |||
Linear Mode
