Function Problems

How to Calculate Scores, Games, Spares, and Ties with Excel Formulas

John Wilson Sep 28, 2026 869 views

Question details

The user needs to construct formulas to calculate game statistics, identify the highest score, find the top scorer, and list all players involved in a tie for the most spares.

How to Calculate Scores, Games, Spares, and Ties with Excel Formulas

Product: Spreadsheets
Device & OS: not provided
Scenario: Creating a dynamic leaderboard or sports scorecard to automatically track game metrics, high scores, and tied players.
Observed behavior: Requires robust formulas to extract specific maximum values and conditionally concatenate multiple matching names from a data table.

Before you start

Ensure your dataset is organized into clear columns without merged cells, such as Column A for Player Names, Column B for Scores, and Column C for Spares, to make formula referencing accurate and straightforward.

Solution 1

Find the Highest Score and Top Scorer

Use the MAX and XLOOKUP functions to extract the highest score and identify the player who achieved it.

The MAX function scans a range for the highest numerical value. Once you have that value, XLOOKUP (or INDEX and MATCH) can search for that maximum score and return the corresponding player's name.

Identify the Highest Score

Select a blank cell and type =MAX(B2:B20) (assuming Column B contains your scores). Press Enter to display the highest score.

Find the Top Scorer's Name

In an adjacent cell, use the formula =XLOOKUP(MAX(B2:B20), B2:B20, A2:A20). This formula searches for the maximum score in Column B and returns the corresponding name from Column A.

Solution 2Recommended

List All Players Tied for Most Spares

Combine TEXTJOIN with conditional logic (IF) to handle scenarios where multiple players share the exact same top score or spare count.

Solution 3

Count Total Games Played

Use the COUNT or COUNTA function to automatically tally how many games have been recorded in the dataset.

Effortlessly Calculate Sports Scores and Leaderboards with WPS Spreadsheet

WPS Spreadsheet fully supports advanced data analysis functions like MAX, XLOOKUP, and TEXTJOIN array formulas, making it incredibly simple to build dynamic scorecards, track games, and resolve ties without complex workarounds.

1. Import Your Data: Open WPS Spreadsheet and input or open your existing game scorecard containing names, scores, and spares.
2. Calculate Maximums: Use the =MAX(range) function to instantly identify the highest scores or highest spare counts.
3. Resolve Ties dynamically: Type the =TEXTJOIN combination formula to cleanly extract and list multiple tied players in a single cell.
4. Save Your Leaderboard: Save your file seamlessly in .xlsx format, ensuring full compatibility if you need to share it with other spreadsheet users.

100% compatible with Microsoft Excel formulas, formatting, and file typesIncludes advanced modern functions like TEXTJOIN and XLOOKUP for freeLightweight application that runs smoothly on Windows, Mac, and mobileBuilt-in robust array calculation engine to handle complex tied-score logics effortlessly

microsoft office alternative - wps office

Frequently Asked Questions

How do I handle multiple players tied for the top score?

You can use the exact same TEXTJOIN and IF formula logic used for spares. Simply point the criteria to your Scores column: =TEXTJOIN(", ", TRUE, IF(ScoresRange=MAX(ScoresRange), NamesRange, "")). This outputs a comma-separated list of all tied top scorers.

Why is my TEXTJOIN formula returning a #NAME? error?

The #NAME? error typically occurs if you are using a much older version of Excel that does not support the TEXTJOIN function. Upgrading to a newer version or using a modern free alternative like WPS Spreadsheet will resolve this.

How can I count games only if a score is actively entered?

Use the COUNT function (for example, =COUNT(B2:B50)). The COUNT function evaluates only cells containing numerical data, meaning any player rows without a recorded score will be ignored in the game total.

What formula should I use to find the second-highest score?

You can use the LARGE function instead of MAX. Entering =LARGE(B2:B20, 2) will return the second-highest numerical value in that specific range. You can change the '2' to any rank you need.

Excel & Data Problems