RANK Function in Excel

What is the RANK Function in Excel?

The RANK function in Excel determines the rank of a specific number in a list of numbers. Its primary purpose is to help you see how a number compares to others in the same list. The RANK function can also sort numbers in either ascending or descending order.

Here's a basic breakdown of how the RANK function works:

Syntax

RANK(number, ref, [order])

• number: The number whose rank you want to find.
• ref: An array of, or reference to, a list of numbers.
• order: An optional argument where 0 (or omitted) sorts numbers in descending order (i.e., larger numbers have a lower rank), and 1 sorts numbers in ascending order (i.e., smaller numbers have a lower rank).

Example Usage

=RANK(A2, A$2:A$10, 0)

This formula would calculate the rank of the value in cell A2 within the range A2 to A10, ranking higher numbers with a lower rank number.

The RANK function is beneficial in statistics, competitive analysis, and scenarios where performance or outcomes relative to a group must be evaluated.

Want to Become a Data Analyst? Learn From Experts!

Data Analyst Master’s ProgramExplore Program

RANK Function Formula

To use the RANK function in Excel, you must provide specific inputs according to its syntax. Here's a detailed explanation of how to construct a formula using the RANK function:

Syntax

RANK(number, ref, [order])

• number: The number you want to find the rank of.
• ref: A range of cells containing the numbers against which the rank will be evaluated.
• order (optional): This argument determines the order of ranking. If it is 0 or omitted, the numbers are ranked in descending order (higher numbers receive a lower rank). If it is 1, the numbers are ranked in ascending order (lower numbers receive a lower rank).

Example Usage

Suppose you have a list of sales figures in cells B2 through B10, and you want to find out the rank of each sales figure in this list in descending order (so higher sales figures get a lower rank):

Basic Formula Without the Order Argument (Default Descending Order):

=RANK(B2, $B$2:$B$10)

Here, B2 is the cell with the number whose rank you want to determine. $B$2:$B$10 is an absolute reference to the range of numbers against which the rank is calculated.

Formula With the Order Argument Set to Descending:

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

This explicitly sets the ranking to descending, though it's unnecessary since descending is the default.

Formula With the Order Argument Set to Ascending:

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

This changes the ranking so that lower numbers are ranked lower.

You would drag this formula down from B2 to B10 to rank each number in the range based on its position within the specified order.

Become a Data Analytics Expert in Just 8 Months!

With Purdue University's Data Analytics PG ProgramLearn More

How to Use the RANK Function in Excel?

Using the RANK function in Excel is straightforward once you understand its parameters and how they affect the ranking results. Here's a step-by-step guide on how to use the RANK function effectively:

Step 1: Prepare Your Data

Ensure your data is organized in a column or row where you can easily reference it. For example, you might have a list of scores, sales figures, or other numerical data you want to rank.

Step 2: Insert the RANK Function

• Select the Cell: Click on the cell where you want the rank result to appear.
• Enter the Formula: Start typing =RANK( and Excel will typically show a tooltip with the syntax to guide you.

Step 3: Define the RANK Function Parameters

• Number: Click on the cell containing the number you want to rank or type its cell reference.
• Ref: Highlight the range of cells against which the number will be ranked or type the range reference. If you copy the formula to other cells, use absolute references (with $).
• Order (optional): Enter 0 for descending order (default, where higher values have a lower rank) or 1 for ascending order (where lower values have a lower rank).

Example

Suppose you have a list of monthly sales figures in column A (from A2 to A10), and you want to rank these sales figures in descending order. Here's how you would use the RANK function:

• Click in cell B2 to enter the rank for the sales figure in A2.
• Type the formula: =RANK(A2, $A$2:$A$10)
• Press Enter to see the rank of the sales figure in cell A2.
• Drag the fill handle (small square at the bottom right corner of the cell) down from B2 to B10 to copy the formula for the other cells.

Step 4: Copy the Formula

If you want to rank other numbers in the same list, copy the formula to other cells in the column or row adjacent to your data. The absolute reference to the range ensures that the correct array of numbers is used for ranking each time.

Step 5: Adjust and Verify

Review the results to ensure they make sense based on your ranking criteria (ascending or descending). If necessary, adjust the formula or correct any data errors.

Next up, let’s dig deeper into the RANK function and learn more about its variants.

Learn 15+ Advanced Data Analytics Skills and Tools

With Purdue University's Program In Data AnalyticsExplore Now

Understanding the RANK.EQ and RANK.AVG Functions

In Excel and the RANK function, two other variants are beneficial for handling ranking with slightly different behaviors: RANK.EQ and RANK.AVG. These functions are helpful when dealing with datasets that include duplicate values. Here's a closer look at each function and its specific use:

RANK.EQ Function

The RANK.EQ function is the successor to the older RANK function in more recent versions of Excel. It works the same way as RANK but is more explicitly named to clarify its behavior with ties in data.

Syntax

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

• number: The number whose rank you are calculating.
• ref: The array or range of data against which the number is ranked.
• order (optional): Specifies how to sort the ranking. 0 (or omitted) for descending order; 1 for ascending order.
• Behavior with Duplicates: When there are duplicates in the dataset, RANK.EQ gives the same rank to the duplicate values, and the next rank(s) is skipped. For example, if two items are tied for second place, both receive a rank of 2, and the next item ranks 4.

RANK.AVG Function

The RANK.AVG function addresses the issue of ties differently by averaging the ranks that would have been assigned to all the duplicates.

Syntax

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

• number: The number to rank.
• ref: The array or range of data for ranking.
• order (optional): The order of ranking, where 0 (or omitted) sorts in descending order and 1 is in ascending order.
• Behavior with Duplicates: In the case of ties, RANK.AVG will assign the average rank to each of the duplicates. For instance, if two items tie for second place, both get a rank of 2.5, and the next rank would have been 3.

Become a Data Analytics Expert in Just 8 Months!

With Purdue University's Data Analytics PG ProgramLearn More

Example Usage of RANK.EQ and RANK.AVG

Let's consider a dataset of scores: 88, 76, 88, 90 in cells A1 through A4.

Using RANK.EQ:

In cell B1, if you input =RANK.EQ(A1, $A$1:$A$4), the rank for the score 88 (assuming descending order) would be 2, as there is one score higher (90). The other 88 would also get a rank of 2, skipping rank 3.

Using RANK.AVG:

In cell B1, if you input =RANK.AVG(A1, $A$1:$A$4), the score 88 would receive a rank of 2.5 because the ranks (2 and 3) are averaged for the two instances of 88.

When to Use Each

• Use RANK.EQ: Use this when you need a traditional ranking system in which ties are given the same rank and subsequent ranks are skipped
• Use RANK.AVG: When you want a fair representation of ranks, particularly in statistical or analytical scenarios where the distribution of ranks affects the outcome, such as calculating averages or other statistical analyses

Next up, let us learn how to calculate the percentile rank of a number in Excel.

Master Excel and other in-demand data analysis tools with our Data Analyst Program and become the data expert everyone’s hiring! 🎯

Calculating Percentile RANK in Excel

To calculate the percentile rank of a number in Excel, you can use PERCENTRANK.INC() or PERCENTRANK.EXC() which both return the percentile rank as a percentage, and only differ slightly in how they handle the lowest and highest value.

Using PERCENTRANK.INC()

Suppose you have a dataset of monthly sales figures for 10 stores in cells B2 to B11. To calculate the percentile rank of each store's sales including the lowest and highest values, use: =PERCENTRANK.INC($B$2:$B$11, B2)

If one store has the lowest sales (e.g., ₹10,000), its percentile rank will be 0. The store with the highest sales (e.g., ₹95,000) will have a rank of 1.

Using PERCENTRANK.EXC()

Now, if you want to exclude the lowest and highest sales from the calculation to focus on the middle-performing stores, use: =PERCENTRANK.EXC($B$2:$B$11, B2)

In this case, ₹10,000 will not rank as 0, and ₹95,000 will not rank as 1. This is helpful when you want to avoid outliers affecting the results.

Learn 15+ Advanced Data Analytics Skills and Tools

With Purdue University's Program In Data AnalyticsExplore Now

Common Mistakes to Avoid in Excel Ranking

When using ranking functions in Excel, such as RANK, RANK.EQ, and RANK.AVG, there are several common mistakes that users often make. Awareness of these can help you ensure more accurate results and effective use of the functions. Here are some key mistakes to avoid:

1. Incorrect Range References

• Mistake: Do not use absolute references for the range when copying the formula to other cells. This can result in each cell referencing a different range, leading to inconsistent and incorrect rankings.
• Solution: When setting up your ranking formulas, use absolute references (e.g., $A$1:$A$10) for the range. This ensures that the same range is used consistently across multiple cells.

2. Ignoring Ties

• Mistake: Using RANK or RANK.EQ, without considering the implications of how ties are treated, can skew analysis if the specific handling of ties matters in the context of your data.
• Solution: Decide if ties should have the same rank or if their ranks should be averaged. Use RANK.EQ to assign the same rank to ties (with subsequent ranks skipped) and RANK.AVG to average the ranks of ties.

3. Wrong Order Argument

• Mistake: Forgetting to set or setting the wrong order argument, leading to results being sorted in an unintended order (ascending vs. descending).
• Solution: Always check the order argument in your function; use 0 or omit it for descending order, and use 1 for ascending order. Ensure this aligns with your analysis needs.

4. Misunderstanding the Output

• Mistake: Misinterpreting what the rank numbers signify, especially when using RANK.AVG, where fractional ranks can sometimes be confusing.
• Solution: Understand and explain (if necessary) the fractional ranks in RANK.AVG indicates average positions due to ties and is a normal part of the function's output.

5. Using the Wrong Function for Data Analysis Needs

• Mistake: Choosing a ranking function that does not fit the specific requirements of your data analysis or reporting can lead to misleading conclusions.
• Solution: Assess the needs of your data analysis. Use RANK.EQ for standard ranking and RANK.AVG for statistical analysis where tie handling impacts outcomes. Consider other statistical functions if ranking isn't needed.

6. Not Updating Range References When Data Expands

• Mistake: Failing to update the range references in your formulas when new data is added, resulting in new data points not being considered in the rankings.
• Solution: Regularly check and update the ranges in your formulas to include all relevant data, especially after adding new entries to your dataset.

Become a Data Analytics Expert in Just 8 Months!

With Purdue University's Data Analytics PG ProgramLearn More

Conclusion

When you need to assess and compare values to recognize their positions in a dataset, Excel's ranking functions can come in handy. Whether you're analyzing scores, sales, or performance data, these functions help you decipher insights and make better decisions.

Simplilearn's Data Analyst Program  offers comprehensive training on Excel and other essential data analysis tools and skills, that will help you build a suceessful career in this field. 

FAQs

1. What is the rank AVG function in Excel?

The RANK.AVG function in Excel calculates the rank of a number, averaging ranks for ties. It's used as =RANK.AVG(number, ref, [order]), where the order is 0 for descending and 1 for ascending.

2. How do I sort rank in Excel?

To sort ranks in Excel, first calculate the rank using the RANK, RANK.EQ, or RANK.AVG function. Then, select the column with the rank numbers and use the "Sort Smallest to Largest" or "Sort Largest to Smallest" option under the Data tab.

3. What is the use of rank AVG?

The RANK.AVG function is used in Excel to assign an average rank to tied values, ensuring a fair and equitable ranking system; especially useful in statistical analysis where the precise distribution of data points affects results.

4. Can you rank text values with the RANK() formula?

No, the RANK() formula only supports numeric values, not text or alphanumeric entries.

5. What is the error in the RANK() formula in Excel?

Errors occur if the range has text, blanks, or incorrect ascending/descending order values.