What is the formula for ranking in excel with duplicates

Answer

To rank numbers in Excel where duplicates exist, the RANK function is used. The RANK function assigns the same rank to identical values. Depending on your requirement, you can rank in ascending or descending order.

1. RANK Function Syntax

RANK(number, ref, [order])

• number: The value you want to rank.

• ref: The range of numbers to compare against.

• order (optional): Use 0 for descending order or 1 for ascending order. Default is descending (0).

2. Ranking with duplicates

When duplicates exist, RANK gives the same rank to identical numbers. Example:

• Here, both 50s are ranked 2.

• Notice that Excel skips ranks for duplicates. After two values ranked 2, the next rank becomes 4.

3. Ranking without skipping numbers (handling ties uniquely)

To give unique ranks even with duplicates, use the following formula:

=RANK(A2,$A$2:$A$10,0)+COUNTIF($A$2:A2,A2)-1

• RANK(A2,$A$2:$A$10,0) → Gives the standard rank.

• COUNTIF($A$2:A2,A2)-1 → Adds a small adjustment to avoid duplicate ranks.

Example:

• The first 50 is rank 2.

• The second 50 is rank 3 (incremented by COUNTIF).

4. Ranking in ascending order

Change the order parameter to 1:

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

• Smaller numbers get higher ranks.

• Duplicates are handled the same way as in descending order.

5. Notes

1. RANK is compatible with both Windows and Mac Excel with the same syntax.

2. For newer Excel versions, RANK.EQ (for equal ranking with duplicates) and RANK.AVG (for average rank for duplicates) can be used:

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

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

   RANK.EQ → Same as RANK.

3. 
   

• RANK.AVG → Assigns the average rank to duplicates.

Example with RANK.AVG:

Yes · 0 No · 0 Add Comment · 0

Add ·

Report