How to Create Weekly Male and Female Player Averages in Excel with WPS Office
Question details
The user needs a formula-driven Excel workbook that calculates weekly average scores for male and female players from a central results table and excludes players who have not played at least 66% of available games.
- Product
- WPS Office Spreadsheets (Excel)
- Device & OS
- not provided
- Scenario
- A weekly Skittles results workbook stores player, gender, week, games played, and score data and needs accurate weekly male/female averages.
- Observed behavior
- The summary should include only qualifying players based on a 66% participation threshold, with values updating as new weekly results are entered.
Set up one Excel Table named Results with Player, Gender, Week, Games Played, and Score columns. Record or reference the total number of available games for each week so the 66% participation rule can be calculated.
Calculate Weekly Male and Female Averages Using AVERAGEIFS and COUNTIFS
Use this formula-based solution when you want a transparent summary that automatically updates as new weekly results are added and only includes players meeting the participation threshold.
This method uses AVERAGEIFS for the average score, COUNTIFS or SUMIFS for qualifying games, and an IF threshold so only players who meet the 66% rule are included. The formulas use structured references, so the results table expands automatically when you add rows.
Select the data range, go to Insert > Table, confirm the range, and name the table Results. Make sure it has Player, Gender, Week, Games Played, and Score columns.
Type the total available games in a cell such as B1 and the threshold 66% in another cell. Name the cells AvailableGames and Threshold from the Name Box so formulas stay readable.
In the Results table, add a Qualifying column and enter =IF(SUMIFS([Games Played],[Player],[@Player])/AvailableGames>=Threshold,"Yes","No"). If each row is one game, use =IF(COUNTIFS([Player],[@Player])/AvailableGames>=Threshold,"Yes","No").
Create columns for Week, Male Average, and Female Average. In the Male Average cell, enter =IFERROR(AVERAGEIFS(Results[Score],Results[Gender],"Male",Results[Week],[@Week],Results[Qualifying],"Yes"),""). Copy the formula and change "Male" to "Female" for the female column.
Fill the summary formulas down for each week. Add new weekly rows to the Results table, and the averages and qualifying flags update automatically.
Calculate Weekly Male and Female Player Averages in WPS Office Spreadsheets
WPS Spreadsheets can directly build this Excel workbook for you. Open your .xlsx file, use the same Excel Table, AVERAGEIFS/COUNTIFS formulas, and structured references to calculate weekly male and female player averages with the 66% participation rule.
- 1. Download and install WPS Office: Download WPS Office from the official WPS site or your app store, install it, and open WPS Spreadsheets.
- 2. Open your existing results workbook: In WPS Spreadsheets, click Menu > Open, select the weekly results .xlsx file, and confirm the table and formulas appear.
- 3. Set up the results table and helper formula: Use Insert > Table to create or confirm the Results table, then add the Qualifying helper formula with COUNTIFS or SUMIFS and the 66% threshold.
- 4. Enter the male and female summary formulas: Use AVERAGEIFS with Gender, Week, and Qualifying criteria to calculate Male Average and Female Average for each week.
- 5. Save the workbook in Excel format: Click Menu > Save As and keep the .xlsx format so you can share the workbook with Excel users and continue updating results.
Frequently Asked Questions
Why does my player average show as blank?
A blank result usually means the player did not meet the 66% participation threshold. Check the Qualifying helper column and the AvailableGames cell referenced by the formula.
Can I change the 66% participation requirement?
Yes. Put the required percentage in a named cell such as Threshold and reference that cell in the Qualifying formula. Change the cell to 50%, 75%, or another percentage to update all results.
Do these formulas work when males and females are in the same table?
Yes. The AVERAGEIFS formula filters the Results table by the Gender column using "Male" or "Female" criteria, so a single table can feed both summary columns.
What happens when I add a new weekly result row?
If the data is an Excel Table, structured references automatically expand to include new rows. Add the row directly below the table or use Insert Table Rows, and the summary formulas update.
Can WPS Office open an Excel workbook with AVERAGEIFS and COUNTIFS?
Yes. WPS Spreadsheets supports .xlsx files, Excel Tables, and AVERAGEIFS/COUNTIFS/SUMIFS formulas, so the workbook remains compatible with Microsoft Excel.
