1

I've Googled and found many ways to calculate percentile, but most seem to be in regards to an array of numbers as opposed to a single number.

I'd like to know in what percentile I finish in a given race. For example, if I finish 72 out of 853, what percentile is that and what formula will automatically calculate it in Google Sheets?

1

Welcome, Paul! The difficulty in calculating percentiles normally comes from compensating for unknowns, or putting values into 'buckets', or massaging values to make sense under a particular statistical distribution. You've got none of that!

Your "percentile" means "what fraction were below me?" in this context. With 853 racers, 853 minus 72 were after you. Doing the math, that means 781 out of 853 competitors came in after you. So your percentile is 781 divided by 853.

In Sheets form; say your Placing is in A2 and your total Competitors count is in B2. To put your percentile in C2, enter:    = (B2-A2) / B2

enter image description here
Or to do it all in one cell, =1-72/853

This will show you a number from 0.00 to 1.00, which you can then have displayed as a percent with the % button in the Sheets menus.

1
  • Wow, so much easier than the complex rabbit hole I was going down. Thank you!
    – Paul
    Mar 21 '19 at 19:15
0
  • in case you need that already pre-formatted use: =TEXT((B2-A2)/B2, "#.00%")

    0

  • and in case you need to use it on the whole column use:

    =ARRAYFORMULA(IF(LEN(A2:A), TEXT((B2:B-A2:A)/B2:B, "#.00%"), ))

    0

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.