Formula for Calculating Percentile Race Finish in Google Sheets

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?

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`

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.

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

• 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%"), ))`