Salary Percentile help

[lox1985]

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

 

[Jobowo]

You can use the PERCENTILE function but you need a reference population to compare it to (a list of salaries).

 

[lox1985]

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]

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

 

[lox1985]

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]

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]

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

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]

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.

 

[lox1985]

Thanks for the help anyways.