1
$\begingroup$

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.

$\endgroup$
  • $\begingroup$ 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? $\endgroup$ – SWilliams Apr 9 '15 at 2:21
  • $\begingroup$ 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. $\endgroup$ – Breea Apr 9 '15 at 20:12
  • $\begingroup$ 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. $\endgroup$ – Breea Apr 9 '15 at 20:24
  • $\begingroup$ 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? $\endgroup$ – SWilliams Apr 9 '15 at 21:06
  • $\begingroup$ 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. :) $\endgroup$ – Breea Apr 9 '15 at 22:34
0
$\begingroup$

At this point I am thinking that for each survey, you could interpolate any missing percentiles, then take a weighted average of given and interpolated values for each percentile to get percentile estimates for the mixture of all the surveys. The interpolation part will take some work but here is a link that describes the process and gives an example. Try doing this by hand with your own data to develop an understanding of the calculations before trying to code anything in Excel.

A more elaborate process is based on simulated salary data. You could fit statistical distributions to each of your samples, then draw new samples from those fits, in proportion to your original sample sizes. There are various strategies for doing this. The new sample points would represent your best guess of actual salaries. Combine the new samples together and get your percentiles directly from the combined simulated data. This approach would require the assistance of a statistician to develop the distribution estimates and possibly a programmer if you want to put something like this in production.

If you only have to do this once the interpolation method is the easiest way to go and Excel might be sufficient. If you have to do this repeatedly, and you have a lot of surveys to work with, it might be worth it to invest in the more elaborate approach. I'm not sure Excel would make the best platform in that case.

If I can come up with any additional ideas I will update my answer or maybe another reader can suggest improvements.

$\endgroup$
  • $\begingroup$ You have been amazingly helpful. I'll let you know how it turns out! :) $\endgroup$ – Breea Apr 13 '15 at 16:24
  • $\begingroup$ Please up-vote my answer if you find its helpful :) $\endgroup$ – SWilliams Apr 13 '15 at 22:47
  • $\begingroup$ Two more things to consider: throw out surveys if the sample size is just too small, and you might also down-weight surveys that you have less confidence in, based on past experience. $\endgroup$ – SWilliams Apr 14 '15 at 16:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.