Return Percentile instead of Percentrank?

[chasfh]

I have a columns of scores and I want to return the percentile of the score within the column.

For example, of the universe of 281 qualifying players, Cody Bellinger has the highest wOBA, at .555. He is definitely in the 99th percentile of qualifying players.

Using the PERCENTILE function doesn't seem to give me what I need:

=PERCENTILE(U$21:U$1511,$B3)​

Because in this example, I have to supply the percentile upfront ($B3), and the result tells me what wOBA figure satisfies that pre-defined percentile.

I could use the PERCENTRANK function:

=PERCENTRANK(Batting[wOBA],Batting[@wOBA],2)*100​

However, it will tell me the result for Cody Bellinger is 100, suggesting that he is in the 100th percentile. There is no 100th percentile. Flip side, there is also no 0th percentile, yet that's where it tells me the wOBAs of JaCoby Jones, Zack Cozart and Peter Bourjos fall. They should be in the 1st percentile.

Is there a way for me to return the true percentiles at the extremes through an Excel formula, in the way I am looking for it?

 

[DRSteele]

The archaic function PERCENTRANK has been replaced with two new functions, PERCENTRANK.INC and PERCENTRANK.EXC. The one that excludes 0 and 1 is EXC.

 

[chasfh]

Thanks for the tip on that. It partially works: it gets rid of the 100th percentile, but it still retains the 0th percentile. Not a deal breaker.