Salary Percentile help

lox1985

Active Member
Joined
Jun 9, 2009
Messages
321
Hey guys,

I've searched but can't find a definite answer on my issue (simple issue). I'm helping a friend figure out what percentile do salaries fall into.

Sample Data:
<TABLE style="WIDTH: 322pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=429 border=0 x:str><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 140pt; mso-width-source: userset; mso-width-alt: 6838" width=187><COL style="WIDTH: 117pt; mso-width-source: userset; mso-width-alt: 5705" width=156><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 id=td_post_2071455 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=86 height=17>Salary</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 140pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=187>Salary Range - 25% percentile</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 117pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=156>Salary Range - 75% percentile</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="61500"> $ 61,500.00 </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="74376"> $ 74,376.00 </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="102507"> $ 102,507.00 </TD></TR></TBODY></TABLE>

I need to find out where 61500 falls into. Seems like a standard deviation problem or percentile formula but I can't figure it out. I'm trying to figure out what 100% would be at (0% = $0) and then use percentile function but I don't think it will yield correct result.

Please advise.

thanks
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Jobowo

Board Regular
Joined
Oct 21, 2009
Messages
242
You can use the PERCENTILE function but you need a reference population to compare it to (a list of salaries).
 

lox1985

Active Member
Joined
Jun 9, 2009
Messages
321
Thanks for the response. Ultimate data will have Position, Salary, Salara Range.

Would I need to use the lowest salary out of all positions to do this? I need to figure this out by position and if I use every salary range, it will not yield accurate results.
 

Jobowo

Board Regular
Joined
Oct 21, 2009
Messages
242
This is more of a statistics than an Excel question. When you say that a salary is in a percentile, it means a percentile of a given population. What population you pick depends on what you are trying to convey to your audience and what it will mean to them. If you want to say that your salary is in the X percentile of all management salaries (because it is a management salary) then that will say one thing; if you say that it is in the Y percentile of all salaries that is something else. You'll pick the statistic (and comparison population) that is most meaningful.

I went back to have a closer look at your numbers. If you're hoping that you can estimate the percentile for the 61.5 from the two other percentile figures, the answer is no. By the look of it, the 61.5 will obviously be lower than 25% but how low depends on the population. If you're getting those figures from some consulting handbook then you can't
 
Last edited:

lox1985

Active Member
Joined
Jun 9, 2009
Messages
321

ADVERTISEMENT

You are correct in both instances. I need to figure out where the salary falls into having the two numbers available. But I don't have the entire spread of salary ranges (they are based on surveys). Without knowing what 0% and 100% stand at, I cannot figure out the salary percentile.
 

Jobowo

Board Regular
Joined
Oct 21, 2009
Messages
242
Even knowing the lowest and highest figure in the range won't tell you the percentile because you don't know the distribution. If your lowest was 60K and your highest 120K, you could have 90% of the figures within 1% of 120K so with a salary of 118K you'd still only be in the 10% percentile.
 

lox1985

Active Member
Joined
Jun 9, 2009
Messages
321

ADVERTISEMENT

I guess you are right. My goal is to answer this portion of your answer:

Code:
If you want to say that your salary is in the X percentile of all management salaries (because it is a management salary) then that will say one thing

Is that possible from the numbers I have?

Here is updated data:

<TABLE style="WIDTH: 566pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=755 border=0 x:str><COLGROUP><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3185" width=90><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3157" width=89><COL style="WIDTH: 144pt; mso-width-source: userset; mso-width-alt: 6826" span=3 width=192><TBODY><TR style="HEIGHT: 13.2pt" height=18><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 67pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" width=90 height=18>Salary</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 499pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=665 colSpan=4>Salary Range</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" height=18 x:num="61500"> $ 61,500.00 </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="65222"> $ 65,222.00 </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="77095"> $ 77,095.00 </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="105959"> $ 105,959.00 </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="120366"> $ 120,366.00 </TD></TR></TBODY></TABLE>
 

Jobowo

Board Regular
Joined
Oct 21, 2009
Messages
242
Not really since you only have 5 numbers and unfortunately the one you are looking at is the lowest. Using percentile suggests that you have a reasonable population to compare with.

This is really a statistical and perhaps marketing problem so I'll leave you to think of the best solution.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,838
Messages
5,598,383
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top