Excel PERCENTRANK.EXC Function
If you want to follow along with this tutorial, please download the example spreadsheet.
Description
The Excel PERCENTRANK.EXC function returns the rank of a value in a data set as a percentage (excludes 0 and 1) of the data set.
Syntax and arguments
Formula syntax
Arguments
|
Return Value
The PERCENTRANK.EXC function returns a numeric value > 0, and <1.
Remark
1. If the argument x does not exist in the array, PERCENTRANK.EXC function interpolates to find the percentage rank.
2. In Excel 2010, the PERCENTRANK function has been replaced with two functions: PERCENTRANK.INC and PERCENTRANK.EXC.
Error
1. If the x is a non-numeric value, the PERCENTRANK.EXC function returns #NAME? error value.
2. If both the argument x and array contain non-numeric values, the PERCENTRANK.EXC function returns #VALUE! error value.
3. The PERCENTRANK.EXC function returns #N/A error value, if one of the below stations occurs:
- The array is empty;
- The x is smaller than the smallest value in the array;
- The x is greater than the largest value in the array.
4. If the significance < 1, the PERCENTRANK.EXC function returns #NUM! error.
Version
Excel 2010 and later
Usage and Examples
Example 1: Basic Usage
An array in cell B4:B11, to find the relative standing of each cell value in the array, please use the below formula:
=PERCENTRANK.EXC($B$4:$B$11,B4)
Press Enter key to get the first result, and drag autofill handle down to get all results.
Example 2: To find the relative standing of a value not in array
An array in cell B4:B11, to find the relative standing of value 35 in cell F4, please use the below formula:
=PERCENTRANK.EXC($B$4:$B$11,F4)
Or
=PERCENTRANK.EXC($B$4:$B$11,35)
Press Enter key to get the result..
Note: If you want to display results in percentage format, select the result cells, and click the Percent Style in Number group under Home tab.
PERCENTRANK.EXC vs PERCENTRANK.INC
PERCENTRANK.EXC function: finds the percentage rank exclusive of 0 and 1 in the array.
PERCENTRANK.INC or PERCENTRANK: finds the percentage rank inclusive of 0 and 1 in the array.
Supposing there is an array {1;3;5;8;9;11},
PERCENTRANK.EXC and PERCENTRANK.INC return percentages separately as below:
Relative Functions:
-
Excel PERCENTRANK Function
The Excel PERCENTRANK function returns the rank of a value in a data set as a percentage of the data set.
-
Excel MAXA Function
The Excel MAXA function returns the largest value in the supplied set of values.
-
Excel ROMAN Function
The ROMAN function converts a number to a roman number in text.
-
Excel ROUND Function
The Excel ROUND function rounds a number to a specified number of digits.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
