hcparsons12
New Member
- Joined
- Jul 1, 2021
- Messages
- 10
- Office Version
- 365
- Platform
- Windows
I'm finding the percentile rank of different numbers in a set range, depending on user input. Just interested as to what the most accurate method is for doing so within Excel and VBA. I typically use the Rank.EQ/Count method, but I noticed that gives numbers slightly different (like 0.002 different) than PERCENTRANK.INC and PERCENTRANK.EXC. How is that possible? If I calculate manually by dividing the count of numbers beneath the selected number by the total count, I get the same number as PERCENTRANK.EXC. So my final results for a given selection based off the four methods are:
RANK.EQ/COUNT = 0.700
PERCENTRANK.INC = 0.699
PERCENTRANK.EXC = 0.698
MANUALLY = 0.698
I'd like to leave out 100th percentile, so I'm favoring PERCENTRANK.EXC. I'm just wondering what the best or most accurate method is to find percentile rank in Excel or in VBA (preferred). Want to be as accurate as possible. Thanks.
RANK.EQ/COUNT = 0.700
PERCENTRANK.INC = 0.699
PERCENTRANK.EXC = 0.698
MANUALLY = 0.698
I'd like to leave out 100th percentile, so I'm favoring PERCENTRANK.EXC. I'm just wondering what the best or most accurate method is to find percentile rank in Excel or in VBA (preferred). Want to be as accurate as possible. Thanks.