Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

How to Use the Excel PERCENTRANK Function

Excel allows us to get a rank of a value in a list, using the PERCENTRANK function. The rank presents a percentage of values which are lower than a selected value. This step by step tutorial will assist all levels of Excel users in finding a relative position of a value in a list.

Figure 1. The final result of the PERCENTRANK function

Syntax of the PERCENTRANK formula

=PERCENTRANK(array, x, [significance])

The parameters of the PERCENTRANK function are:

  • array – a list of values
  • x – a value from the list for which we want to find the position
  • [significance] – a number of decimal digits of the result. This is the non-mandatory parameter and if omitted, it will be 3.

Setting up Our Data for the PERCENTRANK Function

In column B (“Amount”), we have the list of values. In column C (“The position”), we want to get a relative position for each value in the list.

Figure 2. Data that we will use in the PERCENTRANK example

Find a Relative Position of Values in the List

In the cell C3, we want to get a relative position of the cell B3. In other words, we want to find out the percentage of values which are lower than $1,000.

The formula looks like:

=PERCENTRANK($B$3:$B$9, B3)

The array is $B$3:$B$9, while the x is B3. Note that we need to fix the array, as it’s not changing, only the x cell.

To apply the PERCENTRANK function, we need to follow these steps:

  • Select cell C3 and click on it
  • Insert the formula: =PERCENTRANK($B$3:$B$9, B3)
  • Press enter
  • Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

Figure 3. Using the PERCENTRANK function to find a relative position of a value

As we can see, there are 7 values in column B and $1,000 is greater than 5 of them. That means it’s greater than ⅚ value, which is 0.833. This percentage is the result of the function in the cell C3.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc