How to calculate weekly average in Excel?

Suppose you have a purchase table similar to the screenshot below. This example illustrates how to calculate weekly averages in detail using Excel's helper columns and built-in functions. This method is suitable for most users who prefer step-by-step manual operations and want full visibility into the calculation process.

Step 1: Add a helper column to label each row by its week number:

Enter WeekNUM in Cell D1, and then in Cell D2 enter the formula =WEEKNUM(A2,2). (Here, A2 refers to your purchase date in the Date/Time column. The second argument 2 tells Excel to consider weeks starting on Monday, which fits most business scenarios. If your week starts on Sunday, you can use 1). Then, drag the fill handle down to fill the week numbers for the whole data range. Now you'll see which week each row belongs to.

Step 2: This step allows grouping data by week, but you may want to distinguish identical weeks from different years. In this case, add an extra helper column for the year:

Enter Year in Cell E1. In Cell E2, input =YEAR(A2) (again, A2 is your purchase date cell). Drag the fill handle down, and your data now contains week and year columns for more accurate grouping.

Step 3: Now, calculate the weekly average. In Cell F1, enter Average. In Cell F2, enter the following formula: =IF(AND(D2=D1,E2=E1),"",AVERAGEIFS($C$2:$C$39,$D$2:$D$39,D2,$E$2:$E$39,E2)). After that, drag the fill handle down through the intended range.

This formula calculates the average for amounts in the same week of the same year, and only displays the result at the first instance of each unique (week, year) combination (other rows in the same group show blanks).

Notes:
(1) If multiple entries appear for one week in the same year, only the first corresponding row displays the average value; other rows are left blank for clarity.
(2) D1 and D2 reference the week number columns, E1 and E2 reference the year columns, $C$2:$C$39 is the range with amount values you want to average, $D$2:$D$39 is the week number column, $E$2:$E$39 is the year column; adjust these ranges and references as needed for your dataset.
(3) If you do not need to consider years and want to average by week number only, in F2 use: =AVERAGEIF($D$2:$D$39,D2,$C$2:$C$39) and fill down. This will give the simple weekly average without distinguishing between years. Example result shown below:

Tip: For very large datasets, copying formulas down can slow Excel. In such cases, see the VBA automation or pivot table approach below for a more scalable solution.

If you receive #DIV/0! errors, it usually means there are no matching entries for that week in your dataset. Ensure that your helper columns and amount columns have no blanks or mismatched data types.

This method introduces Kutools for Excel’s Advanced Combine Rows utilities, making it easy to batch-calculate all weekly averages without manually entering formulas. Kutools simplifies complex grouping and averaging tasks, saving time and reducing manual errors—especially when dealing with medium to large lists with repetitive grouping and calculations.

1. In a helper column, enter Week in Cell D1. Then in D2 type =WEEKNUM(A2,2) (A2 = date cell). Fill down to tag every record with its week number. This column allows Kutools to group records by week.

2. Select the table that includes the new Week column, then click Kutools > Content > Advanced Combine Rows. As Kutools operates on selected ranges, ensure your selection covers the relevant data, including the columns to combine and the weekly grouping column.

3. In the opened Combine Rows Based on Column dialog box, set up the following operations:
(1) Click the Fruit column and set it for Combine (using comma as delimiter) to keep fruit names together per week.
(2) For the Amount column, set the operation to Calculate > Average so it automatically provides the weekly average.
(3) Assign the Week column as the Primary Key so grouping is based on weeks.
(4) Click Ok to process.

Kutools will quickly group all records by week, list all related fruits, and display the average in a clean new table, as shown below:

Advantages: Fast processing for large tables, minimal manual error, and clear output. Notes: Ensure your helper columns are correct before starting, as mistakes there will affect the results. If week numbers are repeated for entries in multiple years, you may need to add a Year helper column and group by both Week and Year for accuracy.

The Pivot Table method leverages Excel's interactive summarization capabilities to group your data by week and quickly calculate weekly averages. This approach is best for users who prefer visual drag-and-drop, flexible data exploration, and easily updating results when your source data changes. It's practical for large datasets and avoids manual formulas.

Step1: Add a helper column to your table for the week number. In the first cell of a blank column (e.g., D2), enter: =WEEKNUM(A2,2), referencing your purchase date. Fill it down to tag each row with its week of the year.

Step2: With your entire table selected (including the new Week column), go to Insert > PivotTable. In the dialog, confirm your range and choose a new or existing worksheet for placement.

Step3: In the Pivot Table Field List pane:

• Drag the Week column to the Rows area.
• Optionally, to separate data by year, also drag a Year column (created with =YEAR(A2)) to Rows above Week.
• Drag the Amount column into the Values area. Click its dropdown > Value Field Settings and set to Average.

The Pivot Table will now display the average amount for each week or (year, week) group automatically. You can refresh the table any time after adding new data to update the results instantly. This method is especially flexible for exploring other summary statistics or re-grouping by different time periods as needed.

Tips: If your data spans more than one year, always include both Year and Week fields to avoid combining the same week from different years. If you want to present date ranges for weeks, you can add a calculated helper column that lists the start date of each week for clarity. If you see blank rows or unexpected results, check that your helper columns contain no gaps and that your table is formatted properly as an Excel table (Ctrl+T) for best results.

Advantages: No manual formulas, dynamic updating as data changes, supports large datasets smoothly. Limitations: Requires a helper column for week number; original dates must be in a recognized date format.

Average by year/month/date in Excel

Average timestamps of day in Excel

Average per day/month/quarter/hour with pivot table in Excel