Often you may want to calculate the average value grouped by day of week in Excel.
For example, suppose we have the following dataset and we’d like to calculate the average sales based on day of the week:
The following step-by-step example shows how to do so.
Step 1: Enter the Data
First, enter the data values into Excel:
Step 2: Extract the Day of Week from Dates
Next, type the following formula into cell C2 to extract the day of the week for the date in cell A2:
=TEXT(A2, "dddd")
We can then click and drag this formula down to each remaining cell in column D:
Step 3: Create List of Days of the Week
Next, create a list of the days of the week in column E:
Step 4: Calculate the Average by Day of Week
Next, type the following formula into cell F2 to calculate the average sales value based on day of the week
=AVERAGEIF($C$2:$C$15, E2, $B$2:$B$15)
Click and drag this formula down to each remaining cell in column F:
This tells us:
- The average sales on Mondays were 11.5.
- The average sales on Tuesdays were 30.
- The average sales on Wednesdays were 12.
- The average sales on Thursdays were 15.
And so on.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Calculate Average by Month in Excel
How to Calculate the Average by Group in Excel
How to Calculate 7 Day Moving Average in Excel
