Calculation Issues

How to Calculate Weighted Average Rankings for Multiple Lists in Excel

Muhammad TalhaMuhammad Talha Nov 10, 2026 870 views

Question details

The user needs a scalable way to calculate each person's average ranking across multiple list columns without manually writing new SUMIF and COUNTIF formulas every time a list is added.

How to Calculate Weighted Average Rankings for Many Excel Lists
Product
Spreadsheet
Device & OS
not provided
Scenario
Analyzing ranking data involving many columns where the dataset grows over time, requiring a dynamic solution to aggregate data accurately.
Observed behavior
The goal is to automatically reshape list data and calculate grouped average rankings using advanced dynamic-array functions.
Before you start

Ensure you are using a modern version of your spreadsheet software that supports dynamic array functions such as MAP, LAMBDA, and BYROW, as older versions will not be able to process these formulas.

Solution 1Recommended

Use MAP and LAMBDA for a Semi-Dynamic Calculation

This is the recommended approach for calculating average rankings when you have a predefined list of individuals you want to evaluate.

By combining MAP and LAMBDA with SUMPRODUCT, you can iterate through a specific range of names and calculate their averages across multiple columns in one fluid motion. This removes the need for isolated, manual updates.

1
Prepare your name list

Create a distinct list of the individuals you want to calculate averages for in a single column (for example, cells G2 through G7).

2
Enter the dynamic formula

Select the empty cell next to the first name, and enter the following formula: =MAP(G2:G7,LAMBDA(X,SUMPRODUCT($A$2:$A$11,BYROW(--($B$2:$D$11=X),MAX))/COUNTIFS($B$2:$D$11,X)))

3
Adjust ranges and execute

Ensure that $A$2:$A$11 covers your ranking values and $B$2:$D$11 covers the columns containing the names. Press Enter to generate the results for the entire list instantly.

Use MAP and LAMBDA for a Semi-Dynamic Calculation
Time Saver: Because this formula uses absolute references and dynamic spilling, adding a new name to the evaluation list updates the whole array instantly.
Advanced Spreadsheet Features

Calculate Complex Data Arrays with WPS Spreadsheet

WPS Spreadsheet offers powerful support for advanced dynamic array functions like MAP, LAMBDA, and SUMPRODUCT. Handle complex ranking calculations and massive datasets seamlessly without paying premium subscription fees.

  1. 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the ranking data.
  2. 2. Select your calculation cell: Click the cell where you want your dynamic average rankings to be displayed.
  3. 3. Apply the array formula: Paste your dynamic array formula (such as MAP or SUMPRODUCT) into the formula bar and press Enter to instantly populate the results.
Fully compatible with Microsoft Excel array formulas and syntax.Supports modern functions like BYROW, MAP, and LAMBDA for complex data analysis.Lightweight, fast, and completely free to use for daily calculations.Familiar user interface ensuring zero learning curve.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my dynamic array formula return a #NAME? error?

A #NAME? error typically occurs if your spreadsheet software version does not support newer functions like MAP, LAMBDA, or BYROW. Ensure you are using the latest version of your office suite, such as the newest release of WPS Office, which fully supports modern dynamic arrays.

Can I use standard SUMIF and COUNTIF instead of dynamic arrays?

Yes, but standard SUMIF and COUNTIF formulas require you to manually add new statements (e.g., +SUMIF(...)) every time you add a new column or list. Dynamic arrays simplify this by evaluating the entire range at once, making them much better for scalable datasets.

What does the double hyphen (--) do in these formulas?

The double hyphen, or double unary operator, forces a spreadsheet to convert boolean values (TRUE and FALSE) into numeric values (1 and 0). This is necessary because math functions like SUMPRODUCT and MAX cannot calculate text-based TRUE/FALSE results directly.