PERCENTRANK How does it work

[adrgago]

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!

 

[breynolds0431]

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.

 

[adrgago]

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.