Formula for Ranking in Excel

Microsoft Excel has consistently been the fundamental tool for executives in a wide range of industries. The “Rank Function” is a key tool across the wide variety of effective functions Excel offers for swiftly arranging and evaluating numerical data. This feature simplifies the procedure and supports data-driven decision-making, whether you need to rate student grades, highlight sales achievements, or evaluate market trends.

What is the RANK Function?

The RANK function in Excel is a formula used in mathematics that lets you figure out where a numerical value stands in relation to other values in a provided dataset. Each value is given a rank depending on its numeric scale, signifying whether the value is the highest, the lowest, or any combination of the two. The Excel RANK function returns the rank of a numeric value in relation to a list of other numeric values. RANK assigns the same rank to duplicate numbers.

Your Data Analytics Career is Around The Corner!

Data Analyst Master’s ProgramExplore Program
Your Data Analytics Career is Around The Corner!

Formula/Syntax

The RANK function in Excel tells the order or the rank of the numeric values in comparison with the other values in the data set. The RANK function would determine the rank of the numbers, either starting with the highest value if the data is arranged in descending order or starting with the lowest value if the data is arranged in ascending order. 

The syntax or formula for the RANK function is given as 

= RANK (number, ref, [order])

Where 

Number = value whose rank is to be calculated

Ref = the values against which the rank is calculated. It can be a reference to a list of numbers, a single array or a list of numbers. 

Order = It is an optional element. It determines the number that specifies how to rank the numbers, 0 is used to rank the values in descending order, and 1 is used to rank the values in ascending order.

Learn The Latest Trends in Data Analytics!

Post Graduate Program In Data AnalyticsExplore Program
Learn The Latest Trends in Data Analytics!

How to use the RANK Function in Excel? 

When weighed against a list of other numerical values, the Excel RANK function gives a rank to a numerical quantity. Whenever you want to present a rank for a list of numerical values, apply RANK. It is not important to arrange the values in the list before applying RANK.

Two modes of operation for the rank function are managed by the order parameter. Set the order to zero (0) to rank values in ascending order, with the greatest value rated first.  

Let us look at an example to understand the using the formula for ranking in Excel.

Consider the list of marks scored by students in an examination. Rank the students according to the total marks of the examination. 

 Formula_for_Ranking_in_Excel_1

Alt text: Data set for marks scored by the students

To find the rank of the students in descending order we will use the following RANK formula

= RANK(B2,($B$2:$B$7),0)

Formula_for_Ranking_in_Excel_2

Alt text: Ranks of the students according to their score in descending order

To find the rank of the students in ascending order we will use the following RANK formula

= RANK(B2,($B$2:$B$7),1)

Formula_for_Ranking_in_Excel_3

Alt text: Ranks of the students according to their score in descending order

Understanding the RANK.EQ and RANK.AVG Functions

The RANK function was updated and released in Excel 2010. It is now known as RANK.EQ. It uses the same syntax as RANK and operates in the same way: if multiple values are equally ranked, the highest rank is given to each of those values. (The letter "EQ" stands for "equal").

Syntax for RANK.EQ is given as 

= RANK.EQ(number, ref, [order])

Another option that calculates rank in Excel is RANK.AVG, however, it's only accessible in Excel 2010 and later, including 2013, 2016, and later versions.

Syntax for RANK.AVG is given as 

= RANK.AVG(number, ref, [order])

RANK.EQ and RANK.AVG were recently added to Excel 2010 and later editions to substitute the RANK function. RANK is still functioning in every version of Excel for backward compatibility reasons, although it could stop working in future releases.

If the numerical value cannot be found among the ref, any Excel Rank function will return the #N/A error.

Join The Ranks of Top-Notch Data Analysts!

Data Analyst Master’s ProgramExplore Program
Join The Ranks of Top-Notch Data Analysts!

Common Mistakes to Avoid in Excel Ranking

Even though RANK is a simple and easy-to-implement function in Excel. There are certain points to remember while implementing it. 

  1. Make sure that the dataset's values are all of a single data type (for example, numeric). Integrating numerical and non-numerical data might result in unusual rankings and errors.
  2. When ranking elements in a dataset and using the RANK function to many cells, utilize definite references for the entire data set range (using the dollar symbol "$") to avoid it from fluctuating as you transfer the formula to other cells.
  3. If the RANK function is used on cells with error values (such as #DIV/0!, #VALUE!, etc.), errors may be generated. The precision of the rankings may be compromised by such errors.
  4. Ensure that the RANK function's reference range includes the relevant data range. Rankings may be inaccurate if cells are left out or added in excess.

Conclusion

The rank function in Excel is an important formula that is used in various studies. Professionals use it as an asset to determine the ranks of various datasets. It can be applied to large datasets and important insights that can help in future studies and trends. As the discipline of data analysis is growing, it has become essential to learn these kinds of functions in Excel. The postgraduate program in data analysis could help you with this function and more tools to help you excel in various studies. 

FAQs

1. How do I rank values by group in Excel?

Choose any blank cell next to your data set, type this formula, = SUMPRODUCT(($A$2:$A$11=A2)(B2<$B$2:$B$11))+1 then drag the autofill key down to use this formula on the cells you require. Here A2:A11 is the range that includes the group values. A2 is the foremost cell in the data set, and B2 is the first element in the list that is needed to rank. B2:B11 is the list that includes values that are required to be ranked. 

2. How do I rank two columns in Excel?

Choose a blank cell and input the following formula and press enter =RANK(B2,$B$2:$B$7)+SUMPRODUCT(--($B$2:$B$7=$B2),--(C2<$C$2:$C$7)). Applying the formula to all the required cells using the drag fill handle. B2:B7 and C2:C7 are the two columns that are needed to be ranked.

3. What is the rank Excel average?

The Rank average is the rank of a specific value with respect to all the other values in the dataset. It can be used to find the relative position of a specific value within a set range of values. 

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.