PERCENTRANK How does it work

adrgago

New Member
Joined
Oct 20, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am new to excel and am trying to use and understand the PERCENTRANK formula.

Doc says that when the number you are looking for is in your data set, the formula is : lower values / (lower+upper values).
The problem is when I try to search for a number that is not included in the data set.
Here is a simple data example:

Examples
Data: {0, 0, 5} - Result of PERCENTRANK(A1:A3;1) = 0.6

Data : {0, 2.91 } the result of PERCENTRANK(A1:A2;1) = 0.343

Data : {0, 0, 0, 0, 2.91 } the result of PERCENTRANK(A1:A4;1) = 0.83591

How does excel calculate this 0.6, 0.343 and this 0.83591?

PD: The reason why i use some 0 in the example is because my dataset usually has a lot.

Thanks in advance!
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Hi. I believe that PERCENTRANK is to be used in numerous cells, ranking each number it's evaluating against the total range. So, the x variable in the formula would be the item you are ranking amongst the items in the range selected in the first part. This would not be a "1", but another cell.

In the below example, PERCENTRANK has ranked each number from highest to lowest. That is, the highest number is a 1.00, the second highest is a 0.75, the third highest is a 0.50, etc.

1666297495163.png
 
Upvote 0
Hi. I believe that PERCENTRANK is to be used in numerous cells, ranking each number it's evaluating against the total range. So, the x variable in the formula would be the item you are ranking amongst the items in the range selected in the first part. This would not be a "1", but another cell.

In the below example, PERCENTRANK has ranked each number from highest to lowest. That is, the highest number is a 1.00, the second highest is a 0.75, the third highest is a 0.50, etc.

View attachment 76694
Thanks for your answer.

I also believe that this formula is to rank each number against the total range but from business the formula is set to calculate it over 1, whether it is in the values or not, for reasons unknown to me.

My question is if anyone knows how to perfom this excel calculation by hand to be able to replicate it on different sites.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top