How to Calculate Monthly Average Totals by Name in Excel
Question details
The user needs to calculate the monthly average totals for individuals based on audit dates using Excel formulas.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Summarizing a dataset with names, dates, and audit totals into a table showing average scores per person per month.
- Observed behavior
- The goal is to correctly use AVERAGEIFS with absolute and relative references to group dates by month and calculate averages without manual recalculations.
Ensure your dataset contains a column for names, a column with valid date formats (not text), and a column for the numerical totals you wish to average.
Use AVERAGEIFS and EDATE to Calculate Monthly Averages
This is the most effective method to filter and average data based on multiple criteria, including name and a specific month range.
To calculate an average based on both a person's name and a specific month, you need to establish a date range (start of the month to the start of the next month) within your formula. Using absolute and relative references correctly is critical so you can drag the formula across your entire summary table.
In your summary table, enter the first day of each month (e.g., '10/1/2022') into the header cells (B1, C1, etc.). Format these cells to display only the month name by right-clicking, selecting 'Format Cells', and using a custom format like 'mmmm'.
In the first data cell of your summary table (e.g., B2), enter the formula: =IFERROR(AVERAGEIFS(Sheet1!$J$2:$J$17, Sheet1!$A$2:$A$17, $A2, Sheet1!$B$2:$B$17, ">="&B$1, Sheet1!$B$2:$B$17, "<"&EDATE(B$1,1)), "-")
Ensure the ranges point to your actual data worksheet. The reference $A2 locks the column so the name criteria stays correct as you drag the formula right. The reference B$1 locks the row so the month criteria stays correct as you drag the formula down.
Click the fill handle at the bottom-right corner of cell B2, drag it across your month columns, and then drag it down through all the names in your list.
Easily Calculate Monthly Averages in WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like AVERAGEIFS, IFERROR, and EDATE. You can seamlessly perform complex multi-criteria calculations to summarize your data by person and month.
- 1. Import Data: Open your existing audit dataset in WPS Spreadsheet.
- 2. Prepare the Summary Structure: Create a summary table with names in the first column and start-of-month dates in the header row.
- 3. Apply the Formula: Input the AVERAGEIFS formula to evaluate the names and calculate averages across the specified date ranges.
- 4. Fill the Table: Use the intuitive drag-and-drop fill handle to copy the formula accurately across your entire summary grid.
Frequently Asked Questions
Why is my AVERAGEIFS formula returning an error or zero?
This typically occurs if your dates are formatted as text rather than actual date values, or if your absolute and relative cell references (the $ signs) are placed incorrectly, causing the formula to look at the wrong rows or columns when copied.
How do I format a full date to show only the month name?
Right-click the cell containing the date, select 'Format Cells', navigate to the 'Custom' category, and type 'mmmm' in the Type field. This will display 'October' for '10/1/2022' while retaining the underlying date value required for formulas.
Can I use Pivot Tables instead of formulas to find monthly averages?
Yes. You can insert a Pivot Table, place the 'Names' in the Rows area, 'Dates' in the Columns area (right-click to group them by Months), and 'Totals' in the Values area. Finally, change the Value Field Settings from 'Sum' to 'Average'.
