How to Fix Excel LARGE, INDEX, and AGGREGATE Ranking Formula Errors
Question details
The user needs to troubleshoot incorrect rankings calculated by LARGE, INDEX, and AGGREGATE formulas across different rounds of data.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating competitive rankings for odd and even rounds where scores are displaying incorrectly or producing errors.
- Observed behavior
- Ranking formulas return incorrect scores, duplicate ranks, or error values due to mismatched ranges, unhandled tied scores, or misaligned cell references.
Before modifying your ranking formulas, clear any active filters on your dataset and ensure that your source data ranges do not contain hidden rows or merged cells, as these can silently disrupt array calculations and row indexing.
Verify and Align Formula Source Ranges
Check the source ranges inside your LARGE, INDEX, and AGGREGATE formulas to ensure they correctly map to the intended dataset and do not shift when copied.
When working with complex nested formulas across different segments (like alternating odd and even rounds), the most common cause of ranking errors is mismatched or shifted ranges. If an array size in an INDEX function differs from the matching array, the formula will pull data from the wrong row.
Double-click the cell containing the ranking formula or select it and press F2. This highlights the referenced data ranges in your worksheet with colored boxes.
Ensure your data arrays in LARGE and AGGREGATE functions are locked using absolute references (e.g., changing A2:A100 to $A$2:$A$100) so the evaluation ranges do not shift when you drag the formula down.
Check your formulas to confirm that the range used in your INDEX array and the lookup array in your MATCH or AGGREGATE function start and end on the exact same row numbers.

Handle Duplicate Scores and Ties
Implement a tie-breaker helper column using COUNTIF to prevent ranking formulas from returning the same item multiple times for duplicate scores.
Easily Troubleshoot Complex Formulas in WPS Spreadsheet
WPS Spreadsheet provides robust formula evaluation tools and full support for advanced nested functions like LARGE, INDEX, AGGREGATE, and COUNTIF, making it exceptionally easy to rank your data accurately.
- 1. Open your workbook: Launch WPS Spreadsheet and open your existing Excel file (.xlsx or .xls). All formulas, including complex arrays, will be loaded without losing compatibility.
- 2. Evaluate the formula: Select the cell with the incorrect ranking, navigate to the 'Formulas' tab, and click 'Evaluate Formula'. Step through the calculation sequence to see exactly where the logic fails.
- 3. Fix and verify arrays: Edit the absolute references in the formula bar, then use WPS Spreadsheet's conditional formatting tools on the 'Home' tab to quickly verify that ties are being handled correctly.

Frequently Asked Questions
Why does the LARGE function return a #NUM! error in Excel?
The LARGE function returns a #NUM! error if the selected array is empty, or if the 'k' value (the positional rank you are searching for) is greater than the total number of numeric data points in the array.
How do I ignore errors when calculating rankings?
Use the AGGREGATE function instead of LARGE or SMALL. By setting the first argument to 14 (which corresponds to LARGE) and the second argument to 6, AGGREGATE will automatically bypass any error values in your selected data range.
Why is my INDEX formula returning data from the wrong row?
This mismatch typically occurs when the array referenced in the INDEX function does not align with the dataset evaluated by your matching function. Check that both ranges start and end on identical row numbers (for example, both strictly using Row 2 through Row 100).




