As we discussed earlier, define to rank using the excel function with the RANK.AVG, RANK.EQ or RANK formulas still has some problems. These problems are that the rank is still produced a duplicate rank order or the same value in the table. This excel tutorial will discuss about How to Solve Duplicate Rankings in Excel 2013.
The way to solve the same ranking problem in students is of course by establishing particular criteria that you want first. Because these criteria will determine which ranking model is right for you to use.
In this tutorial, I am using Microsoft Excel 2013, for those of you who use Microsoft Excel 2010 and above, besides using the RANK function, we can also use the RANK.EQ formula to rank values in Excel. These two functions will produce the same ranking value.
Before we continue the discussion about how to solve the duplicate value problem of excel rankings, let’s make a sample of the table first or maybe you already have an excel table to resolve. For example table from me, you can see below:
We will use this data to practice formulas in Excel which we will apply in this tutorial. For data above column C to column H, we type manually. On the other hand, for the JML column, we use the SUM formula to add up the daily score and exam score using the formula in cell J9:
After we get the result from the sum, then we copy-paste it into another cell below it or you can use AutoFill. The column in the table number is what we refer to the value series ranking later. The “RANK” column in cell J9 above uses a formula:
Important Note: For those of you who experience errors, you can try changing between comma (,) or semicolon (;), this is because the Microsoft version and the language used on our respective computers.
Keep pay attention to the $ icon in the excel formula in the image above. Its use intended so that the range reference column in the cell does not change (absolute) so that it will not change later when we copy-paste it to other cells spanning the column “RANK“.
After completing the arrangement of the ranking table data and the excel formulas above and if your watching and keep pay attention, it turns out that the results of the ranking using the Excel RANK function have multiple rank values that are double. Namely at rank or rank 4, 6, and 9.
Our excel tutorial this time will discuss further about resulting in the duplicate ranking problem generated above. And also to solve the problem of How to Make Classroom Rank Excel 2013 Students that we discussed on previous article.
Read Also :
- How to Define Rank In Microsoft Excel 2016
- Multiple Characters In Microsoft Excel
- A Simple Formula to Calculate Value in Excel
Avoid Duplicate Ranking With Excel COUNTIF Formula
To solve the double ranking problem and create a unique ranking or ranking for students, it is relatively easy, one way is to use Excel’s COUNTIF function. As you can see from the results in the image above by using the RANK function, it turns out that we find that there are multiple rankings in the ranking results. Rank 4 have 2 duplicate. Rank 6 have 2 duplicate, and Rank 9 have 2 duplicate. To make a unique ranking for each student and not double because later the students will complain of course when the rank they get is the same as the rank they have with their friends. Therefore, use the following excel formula in cell J9 then copy-paste it down:
Important Note: For those of you who experience errors, you can try changing between comma (,) or semicolon (;), this is because the Microsoft version and the language used on our respective computers
Once again keep watching and look at every formula we have added do not let anything be missed or later, we will experience an error in the formula above. After we copy and paste or use AutoFill. Then the final result of the rankings of the value will be generated like this :
Besides using the excel formula above we can also use formulas like this with similar results:
Second RANK and COUNTIF formulas
Important Note: For those of you who experience errors, you can try changing between comma (,) or semicolon (;), this is because the Microsoft version and the language used on our respective computers
Results of the second formula
By using these two formulas, there will be no duplicate rankings that we will determine later. The drawback of this formula is that only the grades in the first row get the highest ranking of all students. So that there is an impression that student number 2 gets a better grade rating when compared to student number 2 even though the number of grades is the same.
It’s done for the tutorial this time. May it be useful…
Read Also :
