Excel Rank functions are essential for determining the position of a number within a list. However, there’s a notable issue with the Excel rank function: it often fails to calculate the correct ranking. Many users have reported problems with the Excel rank function not working as expected.
In this article, we’ll delve into the RANK function, starting from the basics and progressing to some advanced techniques. If you’ve ever wondered about a function like RANKIF, then this article is for you. We’ll address the issues with the RANK function and provide solutions for achieving accurate rankings in Excel. Additionally, I’ll share a formula that allows you to create conditional ranks, such as determining section-wise rankings in a class.
Understanding the Excel Rank Function
The RANK function tells you the position of a number in a list. It works with three arguments:
=RANK(number, ref, [order])
- Number (required): The value seeking its rank.
- Ref (required): An array, list, or reference to numbers.
- Order (optional): A number specifying the ranking order (0 for descending, 1 for ascending).
Pro Tip: The RANK function tells you the position of a number in a list. It works with three arguments:
How to Use the RANK Function?
Note what Microsoft is saying about the rank function. They kept this function for compatibility with Excel 2007 and earlier. The advice is to use RANK.EQ in all cases.
Let us enter the rank function.
=RANK(D4,$D$4:$D$26,0)
Select the number to find the rank (D4) then select the total range of numbers as a reference (D4:D26).
Press F4 to fix this range. You can enter the order as 0 or 1 to consider the list of scores descending or ascending. We will select 0, as we need the student with the maximum number to rank first. If we don’t put 0, then it will also give the same answer, as 0 is the default value. Now copy this formula until the last row.
Pro Tip: To select the total range press Ctrl+shift+down arrow.
This shows the rank of individual students.
Next, write the RANK.EQ function. Parameter of the RANK.EQ is the same as the RANK function.
=RANK.EQ(D4,$D$4:$D$26)
You can see that both functions give the same output. To understand better, let us sort this data by rank.
Now consider one scenario where two students got 479 numbers. Excel RANKfunction shows both of them in the second position, but there is no third position. If three students get the same number, there will not be any third or fourth positions.
This is the problem with the rank function. In this case, the Excel rank function is not working. Later, I’ll show you the method of writing the correct ranking in this article.
How Excel RANK.AVG Function works?
RANK.AVG is a statistical function in Excel used for ranking a number against a list of numbers. It assigns a rank to a specified number, indicating its relative position within the list. If more than one value has the same rank, the average rank is returned.
=RANK.AVG(number, ref, [order])
- Number (required): The value seeking its rank.
- Ref (required): An array, list, or reference to numbers.
- Order (optional): A number specifying the ranking order (0 for descending, 1 for ascending). The default value is 0.
RANK.AVG handles tied or duplicate values differently from the older RANK or RANK.EQ functions. In the case of ties, RANK.AVG assigns an average rank to the tied values.
Let us write the RANK.AVG function in the same way as we wrote the RANK or RANK.EQ functions.
In our example, students M and P got the same 479 marks. This function gives an average of 2nd and 3rd rank, i.e., 2.5 [(2+3)/2] for each student.
How to get rank without skipping number?
In all the above examples, you have seen that the 3rd rank is missing. Now if we don’t want to skip this number and assign rank, then we can add COUNTIF().
=RANK.EQ(D4,$D$4:$D$26)+COUNTIF($D$4:D4,D4)-1
In this formula, RANK.EQ is written the same as above, and to count how many scores are the same, COUNTIF is applied. Please note that we have to freeze the first address of the range by pressing F4. Next, select the cell (D4) as the criteria. Now we have to subtract 1 so that it matches the rank. By copying this formula, we are getting rank without skipping any numbers.
Let us understand how this COUNTIF is helping us. We’re counting 479 in the range, and as the range increases, so does the count. Let us evaluate the formula at H6. Select the RANK.EQ formula part in the formula bar and press F9. The result is 2, which is the default result of RANK.EQ. Now select the COUNTIF part and press F9. The result is 2, as two students got 479 numbers. In this way, the final result is 2 + 2-1 = 3.
The student who got the 3rd rank by this method may not agree and expect that he or she will also be declared 2nd and the 4th candidate will be declared 3rd. That’s what I call the correct method of ranking, which I will explain at the end of this article, along with a similar formula like RANKIF, or conditional rank. Before that, let us explore the advanced requirements of ranking and their solutions.
Identify the top or bottom performers
In many scenarios, it’s not just about ranking; you might need to identify the top or bottom performers directly. The LARGE and SMALL functions in Excel come to the rescue, offering a straightforward way to extract the N largest or smallest values from a dataset.
Considering the student scores in column D, and you want to find the top 3 scores, you can use the LARGE function:
=LARGE($D$4:$D$26, 3)
This formula extracts the third-highest score.
Similarly, if you want to find the third lowest score, you can use the SMALL function:
=SMALL($D$4:$D$26, 3)
Here, the formula retrieves the third-smallest value from the range D4 to D26. As with LARGE, you can modify the ‘3’ to customize the number of bottom performers you want to identify.
These functions offer a quick and efficient way to pinpoint the N largest or smallest values in your dataset, aiding in focused analysis or recognition of outstanding and struggling performers.
Calculating Percentile Rank
Understanding percentiles is vital in statistics. Use the following formula to calculate the percentile rank of students’ scores.
=RANK.EQ(D4, $D$4:$D$26, 1)/COUNT($D$4:$D$26)
Ranking Data Ignoring Zero Values
Use the following formula to display rank, ignoring zero values, which ensures more relevant rankings
=IF($D4=0, “”, IF($D4>0, RANK($D4, $D$4:$D$26), RANK($D4, $D$4:$D$26) – COUNTIF($D$4:$D$26, 0)))
It is to be noted that the above formula uses the RANK function; hence, when there is a tie, it will give the output the same as the previous example, skipping the next rank. Now we will explore how to display the correct ranking.
Solution to Excel Rank Function not Working
So, till now, we have seen a problem with the Excel RANK function, which is unable to show the correct ranking in case of a tie. We require both the students to be declared 2nd and the next student to be declared 3rd instead of 4th, as done by the RANK function.
Use the following formula:
=SUMPRODUCT((D4<=$D$4:$D$26)/COUNTIF($D$4:$D$26,$D$4:$D$26))
Write the formula in I4 and copy it until I26 to get the consecutive ranking according to the score. Don’t forget to write the bracket and fix the cells as mentioned above.
To understand this, let us evaluate the formula at I7 in the formula bar. Select the first part of the formula (D7<=$D$4:$D$26) and press F9. We are getting four numbers: true and false, as the four scores are more than or equal to 478. Select the next part (COUNTIF($D$4:$D$26,$D$4:$D$26)) and press F9; this time it shows the number of occurrences of each score. {1;2;2;1;1….etc.}. We know TRUE stands for 1. Now, by evaluating, we get (1/1 + 1/2 + 1/2 + 1/1) = 3, which is the correct rank for this score. As a test, if you give 478 to 3 students, then all of them will be declared 3rd.
How to get Conditional Ranking or RANKIF?
Now you have come to the most interesting part of this article. We will write down the section-wise rank of the class. Excel doesn’t have any functions like RANKIF, but that does not stop us from writing our formula.
Here I will give you two formulas. The first formula will give a section-wise rank; this is easy, but it will skip the rank during a tie.
=SUMPRODUCT((D4<$D$4:$D$26)*1,(B4=$B$4:$B$26)*1)+1
Write the formula in J4 and copy until J26 to get the section-wise skipping rank according to the score. To understand this, you can evaluate the part of the formula by pressing F9, as I explained earlier.
You can click the Evaluate Formula in the Formula Tab and then click Evaluate to check each step of execution. The first part checks how many numbers are less than the selected score. Multiplying logical TRUE or FALSE with 1 converts it to 1 or 0. The second part checks how many are in the same section. SUMPRODUCT multiplies each element of the array, then sums and gives the final result.
Now, I will give you a formula to get the section-wise correct rank in a class. This is an array formula; you have to press Ctrl+Shift+Enter to enter it.
=SUM(IFERROR((D4<=@$D$4:$D$26)*(B4=@$B$4:$B$26)/ COUNTIFS($D$4:$D$26,@$D$4:$D$26,$B$4:$B$26,B4),0))
This is an extended version of the earlier correct ranking formula for the class. You can click the Evaluate Formula in the Formula Tab and then click Evaluate to check each step of execution. Here, it checks numbers as well as section data to compare. To evaluate multiple criteria, we have used COUNTIFS in place of COUNTIF.
In conclusion, while Excel’s RANK functions provide powerful tools for sorting and ranking data, it’s important to be mindful of potential issues, particularly when dealing with tied values. I hope this addresses the concerns you had regarding the Excel rank function not working. As demonstrated earlier, the RANK, RANK.EQ, and RANK.AVG functions handle ties differently. However, it’s advisable to use them with caution, especially when precise rankings are crucial. By understanding the nuances of these functions and incorporating advanced formulas like COUNTIFS for additional criteria or ABS for absolute values, you can better navigate ranking challenges. Remember, being aware of these intricacies ensures that your data is accurately sorted, and your analyses are based on reliable rankings.
I am sure this Article has touched all the internet visitors, its really really
nice article on RANK I was searching. Thank you for your research. Keep the good work. I learned at lot from here.
I couldn’t refrain from commenting. Exceptionally well
written!
Thank you for your nice words. It will certainly encourage further writing.