Calculate Average Percentages for a Specific Person in Excel
Question details
The user needs to calculate the average percentage score for a specific individual across a large worksheet containing more than 33,000 rows.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing performance or test scores by summarizing percentage data for individual names in a massive dataset.
- Observed behavior
- The user is looking for a scalable and easy formula approach to find the average of percentages based on specific name criteria that can be dragged down for multiple people.
Ensure your percentages are formatted as actual numbers or percentages in Excel, and that the names in your criteria list exactly match the spelling in your source data to avoid #DIV/0! errors.
Use the AVERAGEIF Function
The AVERAGEIF function is the most direct way to average cells that meet a single condition, such as matching a specific person's name in a large list.
This function evaluates a specified range against your criteria (the person's name) and then calculates the average of the corresponding values in a second range (the percentages). By using absolute cell references, you can easily copy the formula for a list of different people.
Type the target name you want to find the average for in a blank cell outside your main data range (for example, cell E2).
Select the cell where you want the result to appear and type the formula: =AVERAGEIF($A$2:$A$33001, E2, $B$2:$B$33001). In this formula, column A contains the names and column B contains the percentages.
Press Enter to see the result. Click on the result cell, hover over the bottom-right corner until you see a cross icon (fill handle), and drag it down to apply the formula to other names in column E.
Format as Table for Dynamic Auto-Expanding Ranges
Converting your data into an Excel Table allows you to use structured references, ensuring your formula automatically includes new rows if your data grows beyond 33,000 entries.
Use AVERAGEIFS for Multiple Criteria
If you need to average percentages by a person's name AND another condition (such as a specific month, department, or score threshold), use the AVERAGEIFS function.
Calculate Average Percentages Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced formulas like AVERAGEIF and AVERAGEIFS, allowing you to seamlessly process massive datasets with tens of thousands of rows. It offers a smooth, lightweight alternative to Microsoft Excel with identical formula logic.
- 1. Open your dataset: Launch WPS Spreadsheet and open your document containing the names and percentages.
- 2. Access the Function Wizard: Select the cell where you want the average to appear. Go to the Formula tab and click 'Insert Function'.
- 3. Find AVERAGEIF: Type 'AVERAGEIF' in the search bar and select it from the list.
- 4. Input formula arguments: Fill in the dialog boxes: select your name column for 'Range', select your target name for 'Criteria', and select your percentage column for 'Average_range'. Click OK.
Frequently Asked Questions
Why does my AVERAGEIF formula return a #DIV/0! error?
This error occurs if the criteria (the person's name) cannot be found anywhere in the criteria range. It also happens if the name is found, but the corresponding cells in the average range (percentages) are completely blank or contain text strings instead of numbers.
How do I format the formula result as a percentage?
Select the cell containing your formula result. Go to the Home tab on the top ribbon, and click the Percentage Style icon (%) in the Number formatting group. You can also use the keyboard shortcut Ctrl+Shift+%.
Can I use AVERAGEIF with wildcards for partial name matches?
Yes, you can use an asterisk (*) for partial text matches. For example, if you set the criteria to "John*", the formula will average the percentages for anyone whose name starts with 'John', such as John Doe, Johnny, or John Smith.
