Formula Errors

How to Fix Excel Leaderboard Ties and #DIV/0! Errors

Nimra Malik Oct 8, 2026 870 views

Question details

The user is attempting to create an Excel leaderboard but encounters duplicate entries when ratings are tied and #DIV/0! errors for unrated shows.

How to Fix Excel Leaderboard Ties and #DIV/0! Errors

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating a dynamic leaderboard to sort and rank shows based on user ratings.
Observed behavior: The current setup returns duplicate titles for tied ratings, produces #DIV/0! errors for shows missing ratings, and some suggested formulas return #NAME? errors in Excel for the web.

Before you start

Ensure that your source data range is clearly organized in columns (e.g., Show Titles in Column A, Ratings in Column B) and identify which cells are producing the #DIV/0! errors.

Solution 1Recommended

Use Dynamic Arrays (SORTBY and FILTER) for Excel for the Web

This is the most reliable method for newer versions of Excel. It automatically excludes blank or errored data and sorts ties without creating duplicates.

Traditional lookup formulas like VLOOKUP or MATCH often struggle with duplicate values (ties), returning the first match repeatedly. By using dynamic array formulas like SORTBY and FILTER, you can cleanly extract and sort the entire dataset at once.

Handle unrated shows with IFERROR

In your original rating calculation column, wrap your formula in the IFERROR function to replace calculation errors with a blank string. For example: =IFERROR(your_rating_formula, ""). This prevents #DIV/0! errors from breaking your leaderboard.

Filter out blanks and errors

Choose the top-left cell where you want your new leaderboard to start. Use the FILTER function to extract only the shows that have a valid rating: FILTER(A2:B20, B2:B20<>"").

Sort the filtered data

Nest the FILTER function inside a SORTBY function to arrange the remaining titles by their ratings in descending order. Your final formula will look similar to: =SORTBY(FILTER(A2:A20, B2:B20<>""), FILTER(B2:B20, B2:B20<>""), -1).

Use Dynamic Arrays (SORTBY and FILTER) for Excel for the Web

Compatibility Check: Dynamic array functions like SORTBY and FILTER are fully supported in Excel for the Web, Microsoft 365, and WPS Office. If you see a #NAME? error, you may be using an older, incompatible software version.

Solution 2

Use RANK.EQ and IFERROR for Traditional Ranking

If you just need to assign a rank number next to each show rather than extracting a sorted list, RANK.EQ is the standard approach.

Advanced Formula Support

Build Flawless Leaderboards with WPS Spreadsheet

You can easily calculate rankings, cleanly handle tied scores, and automatically filter out errors using advanced dynamic array functions like SORTBY, FILTER, and IFERROR in WPS Spreadsheet.

1. Open your data in WPS: Launch WPS Spreadsheet and open the workbook containing your show ratings.
2. Apply error handling: Use the IFERROR function on your raw data to seamlessly handle #DIV/0! errors for shows without ratings.
3. Generate the leaderboard: Type the SORTBY and FILTER formula into an empty cell to instantly generate a dynamic leaderboard that properly handles ties.

Fully compatible with Microsoft Excel formulas, including RANK.EQ and IFERROR.Supports modern dynamic array formulas for automatic sorting and filtering without #NAME? errors.Free, lightweight, and offers a familiar interface for seamless data management.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel leaderboard formula return a #NAME? error?

A #NAME? error usually occurs when you try to use newer dynamic array functions (like SORTBY or FILTER) in an older version of Excel that does not support them, or if there is a typo in the function name. Using Excel for the Web, Microsoft 365, or modern spreadsheet tools like WPS Office resolves this issue.

How does RANK.EQ handle tied ratings?

RANK.EQ assigns the same top rank to tied values. For example, if two shows tie for 2nd place, both will be assigned rank 2. The next show in the list will be assigned rank 4, skipping rank 3.

Why does my rating calculation produce a #DIV/0! error?

This error happens when a formula attempts to divide a number by zero or by an empty cell. In a leaderboard, this frequently occurs for shows that have zero votes or missing data. Wrapping your formula in IFERROR easily intercepts this error.

Can I use VLOOKUP to build a leaderboard?

While possible, VLOOKUP is not recommended for leaderboards because it stops at the first match it finds. If you have tied ratings, VLOOKUP will repeatedly return the name of the first show it matches, causing duplicates. SORTBY and FILTER are much better suited for this task.

Excel & Data Problems