How to Calculate Averages by Last Name in Excel
Question details
The user needs to calculate the average score for each unique last name in a dataset where the number of rows per person varies.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing score data across multiple columns mapped to specific last names to find individual average scores.
- Observed behavior
- Requires a dynamic formula approach to automatically extract unique names and compute their respective averages without manual calculation.
Ensure your dataset does not contain empty rows within the data range, and verify that the names in your target column are spelled consistently without trailing spaces.
Use UNIQUE and AVERAGEIF Functions
This method dynamically lists unique names and calculates their averages, automatically updating if new data is added.
The UNIQUE function extracts a distinct list of last names, while the AVERAGEIF function computes the mean of the scores associated with each name across multiple columns.
Select a blank cell (e.g., K2) and enter the formula =UNIQUE(F2:F100). Press Enter to generate a dynamic list of distinct last names.
In the adjacent cell (e.g., L2), enter the formula =AVERAGEIF(F2:F100, K2#, A2:E100). The K2# reference ensures the formula spills down automatically to match the unique names list.
Modify the ranges F2:F100 and A2:E100 in the formulas to perfectly match your actual data rows and columns.
Calculate Averages by Name Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like UNIQUE and AVERAGEIF, allowing you to seamlessly analyze and summarize score data by last name.
- 1. Open your dataset in WPS: Launch WPS Office and open your workbook in the WPS Spreadsheet application.
- 2. Apply the UNIQUE function: Type =UNIQUE(F2:F100) in an empty column to automatically list all distinct last names.
- 3. Use AVERAGEIF to get scores: Next to the first unique name, type =AVERAGEIF(F2:F100, K2#, A2:E100) and press Enter.
- 4. Format your results: Select the calculated averages and use the 'Number Format' tool in the Home tab to adjust the decimal places.
Frequently Asked Questions
Why is my AVERAGEIF formula returning a #DIV/0! error?
This error occurs if there are no numeric values in the average range for a specific last name, or if the criteria range does not perfectly align with the data range.
Can I calculate averages if my names and scores are in different sheets?
Yes. You can reference ranges in other sheets by adding the sheet name followed by an exclamation mark, like =AVERAGEIF(Sheet1!F2:F100, K2#, Sheet1!A2:E100).
What if the number of rows keeps growing over time?
To handle continuously growing data, format your original data range as a Table (Ctrl+T) and use structured references in your formulas, or use whole column references like F:F and A:E, though this may slightly impact performance.
Why doesn't the UNIQUE function work in my version of Excel?
The UNIQUE function is a dynamic array function available in newer versions of Excel (Microsoft 365, Excel 2021) and WPS Office. If you are using an older version, you may need to use advanced filtering or a Pivot Table to get a list of unique names.
