I have one data set (Benchmark Results) with a corresponding percentile for each number. I have a second data set (My Results) and I want to see where they fall in relation to the original set.
How should I proceed?
Thank you.
percentile ranking.xlsx
welcome to the forum, pwwexcel. you didnt state what you wish to see in column F. that will help us understand better. i'm guessin F2:
=LOOKUP(E2,$C$2:$C$11,$B$2:$B$11)
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
I looked at Benishiryo's solution. However, it gives 90% for the first value (i.e. it's rounding percentiles UP), whereas I would have thought that it should have returned 80%. See the alternative (attached).
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Thank you, Benishiryo and Glenn! Yes, the first value should actually be around 85.6%, as it is between 7.00 and 7.22. But Benishiryo's formula rounds the number to nearest 10th decimal place.
Is it possible to make it more accurate by giving the solution number a 1000th decimal place (0.856, for example)?
Thanks, again!
Paul
Hi there. Sorry for the delay. I was taking apart an old wooden box, to salvage timber... Both solutions above - either rounding up (benishiryo) or down (me) the percentiles would only ever classify your results into a RANGE (i.e 80-85. or whatever. it sounds like you want an estimate of the percentile, rather than a range. So I have had a go at this for you. I've fitted your data to a 3rd order polynomial & used LINEST to calculate the regression parameters. Any data outside the range of the benchmark are invalid - you've no idea what happens there...
take a look & let me know...
Hi Glen, thank you! That's what I needed.
OK, you're welcome.. Can you please mark the thread as solved and (preferably) say thanks to all who helped by clicking the Add Reputation button at the foot of their post(s).
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks