Solving Ranking Problems in Spreadsheets with Different Formula

 A Guide to Solving Ranking Problems in Spreadsheets

This document outlines a common challenge with ranking data in spreadsheets and provides a robust, step-by-step solution. It is based on a real-world scenario from a School Management System, where accurately ranking students is a mission-critical task Like the issue with Voice in Waiting Management System for Clinics

This guide is designed to inspire developers and system administrators to approach similar problems with confidence and precision, avoiding issues we faced.

Remember The Formula's discussed in this Guide are from Google Spreadsheet / Microsoft Excel. You can Also use the same with LibreOffice or Open Office Spreadsheet

Screenshot 1

The Initial Problem: Skipped Ranks with RANK.EQ

The first issue encountered was the behavior of the RANK.EQ function. We started with RANK.EQ because it's a standard and widely known function for assigning ranks. It's often the first tool developers reach for when they need to sort data and see where each item stands.

However, when two or more students have the same score, RANK.EQ assigns them the same rank but then skips the subsequent rank(s). Like you can see in the Screenshot 1, two students with same number and ranked differently with different formula use. 

We will Discuss the Same with the Examples Below on Each Formula and When and where you can use them to get the perfect results or Ranks. 

Example:

• Student A: 95 marks (Rank 1)

• Student B: 90 marks (Rank 2)

• Student C: 90 marks (Rank 2)

• Student D: 85 marks (Rank 4)

Notice how Rank 3 is skipped. This is because RANK.EQ is a "competitive" rank, meaning it counts how many items have a higher or equal value. In this case, three items (A, B, and C) are higher or equal to D's score.

The formula used was: =ARRAYFORMULA(IF(P12:P="", "", RANK.EQ(P12:P, P12:P, 0)))

This formula, while functionally correct for the RANK.EQ purpose, did not produce the desired "dense" ranking (1, 2, 2, 3, 4).

Exploring Other Ranking Formulas

Before building a custom solution, it's helpful to understand the different types of ranking formulas available and why they might or might not fit the scenario:

• RANK.EQ (Competitive Rank): This is the function we started with. It gives the same rank to ties and skips the next number. This is useful for competitive scenarios where you want to show that a tied group holds a single position, and the next person is placed after them.

• RANK.AVG (Average Rank): This function also handles ties but assigns the average rank of the tied group. For our example above, RANK.AVG would give students B and C a rank of 2.5 ((2+3)/2), and student D would still get rank 4. This is more useful for statistical analysis.

• DENSE_RANK (Dense Rank): This is the ranking style we were ultimately looking for. It gives the same rank to tied values but does not skip any ranks. (e.g., 1, 2, 2, 3). While not a native function in Google Sheets, we built a custom formula to achieve this behavior.

Solution Attempt 1: The COUNTIF Approach

A common alternative to RANK.EQ for a dense ranking is to use COUNTIF. A simple formula that works when dragged down is:

=COUNTIF($P$12:$P$40, ">"&P12) + 1

However, when trying to make this formula dynamic for the entire column using ARRAYFORMULA, it produced incorrect results. This is because COUNTIF with a criteria range (P12:P) doesn't work as an array function in the way one might expect. This is a common limitation of certain functions, and it requires a different approach designed for array-wide operations.

This led to the realization that a more powerful, array-native solution was needed.

Solution 2: The MATCH + SORT + UNIQUE Formula

This approach is the most effective and robust way to achieve a dense ranking in Google Sheets without skipping ranks. The final formula looks like this:

=ARRAYFORMULA(IF(P12:P="", "", MATCH(P12:P, SORT(UNIQUE(P12:P), 1, FALSE), 0)))

Let's break down how this works with our example marks (95, 90, 90, 85):

1. UNIQUE(P12:P): This creates a list of only the unique values from your data: (95, 90, 85).

2. SORT(..., 1, FALSE): This sorts the unique list in descending order (highest value first): (95, 90, 85).

3. MATCH(P12:P, ..., 0): This is the key. For each of your original marks, MATCH finds its position within the sorted, unique list.

   • For 95, it finds it in the 1st position, so the rank is 1.

   • For 90, it finds it in the 2nd position, so the rank is 2.

   • For the second 90, it also finds it in the 2nd position, so the rank is also 2.

   • For 85, it finds it in the 3rd position, so the rank is 3.

This successfully produces the desired dense ranking (1, 2, 2, 3) without skipping any numbers.

The Second Problem: Unexpected Ranks Due to Blank Records

After implementing the new formula, the next issue arose: the highest mark in the visible data (230) was being assigned a rank of 2, not 1.

The cause was a subtle but important detail: the ARRAYFORMULA was being applied to the entire column P, which contained blank cells below the student list. The SORT and UNIQUE functions, when processing the full column, were picking up these blank cells. In some cases, a blank cell can be treated as a value, potentially a low or high value depending on the function.

The formula was technically working, but it was ranking based on a larger, hidden dataset that included the blank rows.

The Final, Robust Solution: Using FILTER for Clean Data

To solve the blank record issue and make the solution robust for any future data, we need to ensure the formula only processes numerical data. This is where FILTER comes in.

The final, recommended formula is:

=ARRAYFORMULA(IF(P12:P="", "", MATCH(P12:P, SORT(UNIQUE(FILTER(P12:P, ISNUMBER(P12:P))), 1, FALSE), 0)))

• FILTER(P12:P, ISNUMBER(P12:P)): This part of the formula first creates a clean list containing only the values from column P that are actual numbers. It effectively ignores any blank rows or text.

By using this FILTER, the SORT and UNIQUE functions will now operate only on the valid marks, ensuring that your rankings are always accurate and consistent, regardless of what's in the rows below your data.

Why This Formula is the Right Choice

This solution is ideal for a mission-critical application like a School Management System for several key reasons:

• Logical Ranking: It provides a "dense ranking" (1, 2, 2, 3, etc.), which is the most logical and fair way to rank students in an educational setting. Tied students share the same rank, and the next student gets the next available rank without any numbers being skipped.

• Data Integrity: The FILTER(..., ISNUMBER(...)) part of the formula ensures that the ranking is only performed on valid numerical data. This makes your system resilient to common data entry errors, such as blank cells or stray text, which would otherwise break the ranking.

• Automation: Using ARRAYFORMULA means the ranking column is always up-to-date and requires no manual intervention. As new marks are added or existing marks are changed, the ranks will update automatically, which is crucial for a dynamic system.

This journey of handling a simple issue of Ranking, with the spreadsheet highlights the importance of understanding how spreadsheet functions interact and how a single robust formula can solve multiple problems. 

Check out also about implementation of Audio in Web apps with Free and Paid versions

Formula Used in these Scenario of Test and Trial:

1. =ARRAYFORMULA(IF(M12:M="", "", RANK.EQ(M12:M, M12:M, 0)))
2. =COUNTIF($M$12:$M$40, ">"&M12) + 1
3. =ARRAYFORMULA(IF(M12:M="", "", MATCH(M12:M, SORT(UNIQUE(M12:M), 1, FALSE), 0)))
4. =ARRAYFORMULA(IF(M12:M="", "", MATCH(M12:M, SORT(UNIQUE(FILTER(M12:M, ISNUMBER(M12:M))), 1, FALSE), 0)))

As a Independent Software and Web developer, encountering and solving these mission-critical scenarios is a huge learning experience, and building a reliable system is what truly matters. 

I hope this guide inspires others to confidently tackle similar challenges.

With this also anticipate some other solutions out in the world for this. Please feel free to comment those and I would love to hear about those.

Thanks.