# How to Calculate Average by Day of Week in Excel

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.