## How to average based on day of week in Excel?

In Excel, have you ever tried to calculate the average of a list number depending on which day of the week? Supposing, I have the following data range, and now I want to get the average orders of all Mondays, workdays or weekends. This article, I will introduce some formulas to help you to solve this task.

#### ** Calculate the average based on day of week with formulas**

**Calculate the average based on a specific day of week**

To get the average based on a specific day of the week, the following formula may help you. In this example, I will calculate the average orders of Mondays of the data range, please do as follows:

Please enter this formula: **=AVERAGE(IF(WEEKDAY(D2:D15)=2,E2:E15))** into a blank cell, and then press **Shift + Ctrl + Enter** keys together to get the correct result. See screenshot:

**Note:** In the above formula:

* D2:D15* is the date range which you based on;

The number * 2* indicates

**Monday**, and 1=Sunday, 3=Tuesday, 4=Wednesdays…, you can change the number 2 as your needed;

* E2:E15* refers to the data range which you want to get the average.

**Tips**: The following formula also can help you to solve this problem: **=SUMPRODUCT((WEEKDAY(D2:D15,2)=1)*E2:E15)/SUMPRODUCT((WEEKDAY(D2:D15,2)=1)*1)** and just press** Enter** key to get the result. (* D2:D15* is the date range which you based on,

*refers to the data range which you want to get the average, the number*

**E2:E15***indicates*

**1****Monday**, 2 = Tuesday, 3= Wednesdays...)

**Calculate the average based on workdays**

If you want to average the orders with all workdays in the range, please apply this formula: **=AVERAGE(IF(WEEKDAY(D2:D15,2)={1,2,3,4,5},E2:E15))**, then press **Shift + Ctrl + Enter** keys together, and you will get the average orders from Mondays to Fridays.

**Notes:**

1. Here is also another formula may do you a favor:**=SUMPRODUCT((WEEKDAY(D2:D15,2)<6)*E2:E15)/SUMPRODUCT((WEEKDAY(D2:D15,2)<6)*1)** and press **Enter** key.

2. In the above formulas: * D2:D15* is the date range which you based on, and

*refers to the data range which you want to get the average.*

**E2:E15****Calculate the average based on weekends**

And if you just want to average the orders in weekends (Saturdays and Sundays), the following formula may do you a favor:

Please type this formula: **=AVERAGE(IF(WEEKDAY(D2:D15,2)={6,7},E2:E15))** into a specific blank cell, and press **Shift + Ctrl + Enter** keys simultaneously, and then you will get only the average orders in weekends. See screenshot:

**Notes:**

1. You can also use the below formula to solve this task: **=SUMPRODUCT((WEEKDAY(D2:D15,2)>5)*E2:E15)/SUMPRODUCT((WEEKDAY(D2:D15,2)>5)*1)** and just press Enter key.

2. In the above formulas: * D2:D15 *is the date range which you based on, and

*refers to the data range which you want to get the average.*

**E2:E15****Related articles:**

How to calculate average between two dates in Excel?

How to average cells based on multiple criteria in Excel?

How to average top or bottom 3 values in Excel?

### Best Office Productivity Tools

**Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel**

**Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...**

Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.

#### Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!