PERCENTRANK.EXC Function: Microsoft Excel is a powerful data visualization and analysis program used all around the world. It uses spreadsheets to store, organize, or perform operations with formulas and functions. If you want to get the relative rank of a value in a data set, you can use the PERCENTRANK.EXC function.
In this post, we will explore how to use the Excel PERCENTRANK.EXC Function with its syntax, explanation, and examples. Get an official version of MS Excel from the following link:
Description of PERCENTRANK.EXC Function
- This function returns the rank of a value in a data set as a percentage of the data set exclusive of first and last values.
- It can be used to evaluate the relative standing of a value within a data set.
- The below image has shown the function behavior of the related functions.
|PERCENTRANK||It will find the percentage rank inclusive of the first and last values in the array.||Inclusive|
|PERCENTRANK.EXC||It will find the percentage rank exclusive of the first and last values in the array.||Exclusive|
|PERCENTRANK.INC||It will find the percentage rank inclusive of the first and last values in the array.||Inclusive|
- Here, you will see the syntax of the PERCENTRANK.EXC function.
- To apply this function on your spreadsheet, you must select a cell and enter the formula in the following format.
- Once you enter the formula, click the Enter button to get the result.
=PERCENTRANK.EXC(array, x, [significance])
- Array – It is the input array or range of data.
- x – The input value for which you want to know the rank.
- Significance – It is an optional one. This argument represents the number of significant digits for the returned percentage value. If it is omitted, it will take three digits by default (For example, 0.294).
1) If the significance is less than 1 and then this function returns #NUM! Error.
2) If the array is empty, it will return #NUM! Error.
3) If any of the arguments are non-numeric values such as letters and symbols and then this function returns #VALUE! Error.
Here are examples to help you to understand this function clearly.
- Initially, you must open your Excel workbook on your PC and launch the worksheet with data.
- In this screenshot, you can be able to see the input range in Column A, and we will get the relative rank of a value in a data set using the PERCENTRANK.EXC function.
- Then, you must enter the formula in the cell below to get the result.
- After entering the formula, you need to click the Enter button to get the output.
- Once you get the output for the first cell, you have to drag down that cell to fill in the remaining values as shown in the below image.
- If the significance is less than 1 and then this function returns #NUM! Error.
- If any of the arguments are non-numeric values such as letters and symbols and then this function returns #VALUE! Error.
From the above examples, you can clearly understand how to use PERCENTRANK.EXC Function.
In this post, you can learn the usage of Excel PERCENTRANK.EXC Function with its syntax, explanation, and examples. Share your feedback in the comment section. To learn more about Excel functions, then visit our webpage, Aawexcel.com.
The following video will show you how to apply the PERCENTRANK.EXC function in the spreadsheet.
- How to Use Excel FLOOR.MATH Function?
- How to Use Excel FLOOR Function?
- How to Use Excel CEILING.PRECISE Function?
- How to Use Excel CEILING Function?
- How to Use Excel GCD Function?
Hi there, I’m Sridhar – an Excel enthusiast with over 10 years of experience working with software. I’m passionate about using Excel to solve complex problems and streamline business processes. Over the years, I have helped businesses of all sizes to improve their operations and save time and money.
Aside from working with Excel, I also enjoy writing and sharing my knowledge with others. You’ll often find me contributing to the AAW Excel blog, where I provide tips, tricks, and tutorials that are easy to understand for readers of all skill levels.