# Percentiles and Salary Surveys

What formula would I use (using Excel, preferably) to determine the 'average' 10th percentile rank value if I have four different and yet related 10th percentile values? Can I just average the values I have for four different 10th percentiles and use that number as the 'average percentile'? Most importantly, is there such a thing as an average percentile? Or, can I use a range of numbers in the same percentile to determine an 'overall percentile'?

I've written out really basic example of what I'm looking at in case the question itself was unclear. Note that I'm also using really simple numbers and I ONLY have percentile values and not the raw data used to assign the percentile values.

Category X

$50 = 10th percentile$60 = 25th percentile

$70 = 50th percentile Category Y$80 = 10th percentile

$90 = 25th percentile$100 = 50th percentile

Category Z

$110 = 10th percentile$120 = 25th percentile

\$130 = 50th percentile

I need to figure out the percentile rank for X number. The rank must take into account all categories and since the purpose of this work project is to make the data simple, I need to assign X an overall percentile rank.

• Do you know how big each of the category surveys is (how many salaries in each survey), and do you know more of the percentiles from those surveys than the three you show in your example? Say min, 10th, 20th... 90th, max? – SWilliams Apr 9 '15 at 2:21
• I have the total number of responses provide for the categories that apply (or sub categories, really, since the main category is the position being analyzed). I have the 10th, 25th, 50th, 75th, and 90th percentiles. I also have a weighted average, simple average, avg min, mid, and max. – Breea Apr 9 '15 at 20:12
• And what I was thinking (instead of averages) is using the =percentile(array, k) function in excel to select the rank of values in each percentile category and determine the value that way. So, selecting all the 10th percentiles for a certain category and finding the 10th percentile from those percentiles, but again I have no idea if that's actually statistically accurate. – Breea Apr 9 '15 at 20:24
• I suspect the technique you are proposing would give extreme values, especially if the salary data tends to have a mound-shaped distribution with long tails. In other words, if the 10th percentiles tend to be small, then your method would tend to give you the smallest of the small. Not sure if that is what you want. Stretches things the other way with the 90th pctl. If you know the sample sizes of each survey, one approach would be to take a weighted average of the percentiles, using the relative survey sizes as the weights, but I will look in to this more. Are you in HR by chance? – SWilliams Apr 9 '15 at 21:06
• I am in HR, yep. I've tried using weighted percentiles but it's difficult to do (for me, at least) without having all of the data points. I'm open to trying any magical statistical tricks you know of, though. :) – Breea Apr 9 '15 at 22:34