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