WPS Spreadsheet: Calculate Group Averages from a Separate Excel List
Question details
The user needs to calculate average values for each group when names and values are stored in one table and group assignments are stored in a separate list.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A ranking table contains names in column A and values in column B, while another list identifies which people belong to a selected group. The result should be calculated without adding a group column to the ranking table.
- Observed behavior
- The group average must be calculated by matching names across the separate lists and averaging only the corresponding values.
Confirm that the names in both lists use matching spelling and that the name and value ranges contain the same number of rows. Identify the cell or range containing the selected group and the group-member list before entering the formula.
Use FILTER and XMATCH to calculate the group average
Use this method when the separate list already contains the members of the selected group and your WPS Spreadsheet version supports FILTER and XMATCH.
The formula filters the values in the ranking table to keep only names found in the selected group list, then calculates the average of the remaining values. Replace the example ranges with the ranges in your workbook.
Open the ranking worksheet and select the cell where you want to display the average for the selected group.
Enter =AVERAGE(FILTER($B$1:$B$20,ISNUMBER(XMATCH($A$1:$A$20,E2)))) in the formula bar, replacing $A$1:$A$20 and $B$1:$B$20 with the ranking name and value ranges and E2 with the separate group-member list or reference.
Press Enter to calculate the average, then copy or drag the formula to other group result cells as needed.
Filter group members from a two-column assignment table
Use this variation when the separate sheet has one column for names, one column for group assignments, and a cell containing the group to calculate.
Calculate Separate-List Group Averages in WPS Spreadsheet
WPS Spreadsheet can match names between worksheets, filter the values belonging to a selected group, and calculate each group average without modifying the original ranking table. Its familiar formula interface supports modern functions such as FILTER and XMATCH for this workflow.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheet, and open the workbook containing the ranking and group-assignment lists.
- 2. Check the source ranges: Verify the ranking names and values, then identify the separate group-member or group-assignment ranges.
- 3. Enter the formula: Select the result cell and enter the FILTER and XMATCH formula that matches ranking names to the selected group.
- 4. Review the result: Press Enter, verify that the returned average includes only members of the selected group, and correct any spelling or range mismatches.
Frequently Asked Questions
Can I calculate the average without adding a group column to the ranking table?
Yes. FILTER and XMATCH can match the ranking names against a separate group-member list, so the original ranking table does not need an additional group column.
What should I replace E2 with in the formula?
Replace E2 with the reference to the separate list or group-selection cell used in your workbook. If the assignment data has separate name and group columns, use the two-column formula and set E2 to the selected group name.
Why does the formula return an error or no result?
Check that names match exactly, including spaces and spelling, and that the name and value ranges have equal lengths. Also confirm that the selected group contains at least one matching name.
Can I calculate averages for several groups at once?
Yes. Put each group name in its own result row, reference the corresponding group cell in the formula, and copy the formula down.
Does WPS Spreadsheet support Excel XLSX files for this calculation?
Yes. WPS Spreadsheet can open and save XLSX workbooks while preserving common formulas, worksheets, and spreadsheet formatting.
