Using RANK, RANK.EQ, and RANK.AVG formulas
The Ranking Formula in Excel 2013 – Sometimes, we as teachers are facing with student grades that we want to rank to find the highest and lowest scores of a class. Then, how do you define rank in Excel? We need to know that Microsoft Excel itself has provided a RANK function that can be used to find and define the rank of each student in a list of numeric series values.
Starting in Microsoft Excel 2010 and above until the current new version, Microsoft Excel has started adding the RANK.AVG function and also the RANK.EQ functions as an alternative to Excel’s RANK function. Although we can still use the rank function up to the current latest version.
That’s why don’t be surprised why those of us who are new to Microsoft Excel 2007 and below are likely to be confused because they won’t find the RANK.EQ and RANK.AVG formula functions..
In this excel tutorial, I will inform a few steps about how to use all three excel functions.
Basically, the function of these three excels is RANK, RANK.AVG and RANK.EQ is used to rank in excel or search for rank score in the form of numbers relative to a series of list values.
For example, if we have a series of values list, then the above three excel functions will sort the list of values either ascending(from the smallest score to the largest score) or descending(from the largest score to the smallest score).
Understanding how these three functions more deeply in the Excel formula, we need to create a data table like the one below:
If we have created the table or you have other tables that can also be applied to these three functions, now let’s discuss one by one how to rank and/or rank values on existing excel table data..
RANK formulas in Excel
In Microsoft Excel, this function is a formula that is often used to rank a list of values in a particular table. In short, the RANK function is an excel function that is intended to determine the ranking of a group of values.
How to use the RANK formula
Writing the RANK formula follows the rules below:
Description :
- number: the value of the number that we will look for in the rank.
- ref: a series of values with numbers in a table in the form of an array, cell reference or a series of numbers as a reference to determine the ranking.
- [order]: number 0 or 1 as a benchmark for sorting the list of values (ref) to determine ranking.
| – If the value is 0 the value will be sorted ascending i.e. from the smallest value to the largest. |
| – If the value is 1, the number will be sorted in descending order, namely from the largest value to the smallest value. |
Example of a RANK formula in Excel
In the example table above, write the following excel formula in cell R9:
In the excel formula, the value you are looking for is cell N9 in the tabel JML table with a reference list of values $N$9:$N$18. Are you confused about why there should be a $ sign in the excel formula this time? This is indicated so that when we want to copy down the formula, the range of each data or list of values in the table does not change.
After calculating the excel formula copy or use auto-fill series for the cells below it (N10 to N18).
The results obtained will be like the image below:
From the results of the use of rank formula Excel above can be obtained the first-grade rank is the student with the sequence number 8, while the 2nd rank is the student with the sequence number 9 and for the other students follow the existing rank.
Excel’s RANK.EQ formula
This second function is RANK.EQ for this formula itself is to rank the same value as by the RANK formula. This because of the RANK.EQ formula intended to replace the RANK formulas in Microsoft Excel 2016.
How to use the RANK.EQ formulas :
The syntax in calculating the formula RANK.EQ in the excel formula can be seen as follows:
Explanation:
Regarding the argument RANK.EQ itself is the same as the previous RANK function that we discussed above.
- number: the value of the number that we will look for the rank or ranking.
- ref: a series of values with numbers in the table in the form of an array, cell reference, or a series of numbers as a reference to determine the ranking.
- [order]: number 0 or 1 as a measure for sorting the list of values (ref) to define the ranking.
| – If the value is 0 the value will be sorted ascending i.e. from the smallest value to the largest. |
| – If the value is 1, the number will be sorted in descending order, namely from the largest value to the smallest value. |
Example of the RANK.EQ in Excel Formulas
The sampling formula this time still uses the data table used. In the table, example write the following excel formula in Cell N9:
Then copy and paste that excel formula for the other cells below. So that the results of the ranking using the RANK.EQ formulas are obtained as follows:
By using order of 0 or emptied then the biggest value will be given the highest rating. If we want the smallest value to get the highest ranking then change the argument by 1.
Formula RANK. AVG Excel
The formula RANK.AVG is a replacement formula for RANK which was introduced since the release of Microsoft Office Excel 2010 versions. Excel RANK.AVG function added to Microsoft Excel 2010 is used for rank values in a value list table by adding the .AVG (AVERAGE) argument the average number of values in a cell. With this formula, if there is the same value, Excel will immediately produce the average number of rankings or rankings of values that have the same rank. This formula was different from the two formulas that we discussed above. where if there is the same value, then all groups of the same cell values will get the same ranking according to the order.
How to use the RANK.AVG formula :
Explanation :
The explanations for the arguments of the RANK.AVG function is the same as the arguments for the RANK function.
- number: the value of the number that we will look for the rank or ranking.
- ref: a series of values with numbers in the table in the form of an array, cell reference, or a series of numbers as a reference to determine the ranking.
- [order]: number 0 or 1 as a measure for sorting the list of values (ref) to define the ranking.
| – If the value is 0 the value will be sorted ascending i.e. from the smallest value to the largest. |
| – If the value is 1, the number will be sorted in descending order, namely from the largest value to the smallest value. |
Example of Using the RANK.AVG Formulas in Excel
Still using the same table, in the example table above, write the following excel formula in cell N9. At this time we will count 2 numbers at once in a row of column numbers. I did this so that you don’t get confused if you have problems if you have the same score in a series of numbers value. The first experiment used cell N9:
Then copy and paste the formula for other cells below it, or we can use the auto fill series by dragging it from the cells that we have counted to the cells we want. The result of the ranking that we get using the RANK.AVG formula is:
However, if you attend to the results of the RANK.AVG formulas in the excel table above, then the value we get is the same as the previous formulas. This because the JML table does not have the same score. Then, what if in the value series there are the same numbers? How much value will we get on our ranking ?
In this second experiment I will use the RAT table. In the second experiment, write the following excel formula in cell J9. You can see it below:
The results are as follows:
The conclusion of these three formulas, is :
- The RANK formula and the RANK.EQ formula returns the same rank for the same value that is double or duplicate.
- The number of equal or duplicate values in the RANK and RANK.EQ functions will have an effect on the next ranking.
- With the RANK.AVG function, if there are duplicate values, RANK.AVG will take the average of the duplicate ranking values. Although several examples like the one above we find the same rank.
- The dollar symbol ($) in the formula or Excel function above intends to patent the data range so that when copied to another cell, the value of the range does not change.
After learning these three functions, if we want to make a name order according to the ranking. In fact, there are still some problems in finding a ranking or ranking value using the excel formulas RANK, RANK.AVG dan RANK.EQ.
Some of these problems are how to avoid the same rank resulting from the ranking formula we’ve discussed. So from me thank you for following the tutorial this time. See you in the next tutorial. 🙂
