I have a sample data from which i am calculating the rank
import pandas as pd
df = pd.DataFrame({"value": [500,500,200,101,100,72,63,55,50,30,30,20,1]})
print(df)
print(df["value"].rank())
print(df["value"].rank(pct=True))
The results are as follows
value rank perc
0 500 12.5 0.961538
1 500 12.5 0.961538
2 200 11.0 0.846154
3 101 10.0 0.769231
4 100 9.0 0.692308
5 72 8.0 0.615385
6 63 7.0 0.538462
7 55 6.0 0.461538
8 50 5.0 0.384615
9 30 3.5 0.269231
10 30 3.5 0.269231
11 20 2.0 0.153846
12 1 1.0 0.076923
I want to calculate the rank and perc columns in excel
if i try to apply this formula =RANK.AVG(A1,$A$1:$A$13,0), i am getting different ranking numbers
1.5
1.5
3
4
5
6
7
8
9
10.5
10.5
12
13
Can someone help me with the excel formula. I want to reproduce the same results as in the dataframe.
What changes i have to do in the EXCEL formula to achieve the values shown above in the dataframe?
4
Explanation about excel :
The formula =RANK.AVG(A1, $A$1:$A$13, 0) in Excel calculates the rank in descending order (largest to smallest), whereas pandas ranks in ascending order (smallest to largest) by default.
To reproduce the pandas behavior, you need to reverse the ranking order. This can be done by using the formula:
=RANK.AVG(A1, $A$1:$A$13, 1)
- Here, the 1 parameter specifies ascending order, matching the default behavior of pandas.
Now, for the percentile :
To calculate the percentage rank (similar to the rank(pct=True) in pandas), you can use the PERCENTRANK.INC function in Excel:
Percentile Rank Formula in Excel:
=PERCENTRANK.INC($A$1:$A$13, A1)