Chinese-style ranking or parallel ranking is a common ranking method, characterized by its ranking is a continuous integer, as shown in the figure below, ranking sales, "Lin Ling"'s sales Chinese-style ranking is the number 2, the standard ranking function in excel rank ranking number is 3, skipping the number 2.
Enter the formula in cell d2 and drop down to fill: =sum(1 countif($b$2:$b$7,$b$2:$b$7)*(b$2:$b$7>=b2)).
Let's break down this array formula in detail:
For array formulas, versions before excel2021 need to enter ctrl+shift+enter;
b$2:$b$7>=b2:logical array, compare whether the value of each cell in the b2:b7 region is greater than or equal to the value of the current cell (b2-b7), b2 to b7 are returned as follows;
1 countif($b$2:$b$7,$b$2:$b$7): The countif function calculates the number of occurrences of each cell in the b2:b7 region, and returns an array, taking the reciprocal as the reciprocal;
1/countif(..Multiply the reciprocal with the logical array and get the following result;
sum(..Add all the values in the array obtained in the previous step to get the Chinese ranking.
I hope this article is helpful to you, your likes and favorites are the biggest motivation for me to continue to update the article, thank you for your support.