Skip to main content

How to average based on day of week in Excel?

doc-average-by-week-1

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


arrow blue right bubble 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:

doc-average-by-week-2

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, E2:E15 refers to the data range which you want to get the average, the number 1 indicates 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.

doc-average-by-week-3

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 E2:E15 refers to the data range which you want to get the average.


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:

doc-average-by-week-4

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 E2:E15 refers to the data range which you want to get the average.


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

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


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!
Comments (13)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I have some data, that is in one day i have some order and with different quantity, how do i get the formula for average quantity in one singel date?
This comment was minimized by the moderator on the site
12-Dec-22 1 Week 51 Mon W-Day 86.85
13-Dec-22 1 Week 51 Tue W-Day 83.57
14-Dec-22 1 Week 51 Wed W-Day 89.67
15-Dec-22 1 Week 51 Thu W-Day 89.67
16-Dec-22 1 Week 51 Fri W-Day 85.45
17-Dec-22 1 Week 51 Sat PH 80.28
18-Dec-22 1 Week 51 Sun FH 85.45

I want the above data average for monday to friday(W-Day) average ,Saturday(PH) and Sunday(FH) average
This comment was minimized by the moderator on the site
Hello, Manigandan
If you need to average from Monday to Friday and average from Saturday and Sunday, please apply the below formulas:
Average from Monday to Friday: =AVERAGE(IF(WEEKDAY(A2:A8,2)={1,2,3,4,5},D2:D8))
Average from Saturday and Sunday: =AVERAGE(IF(WEEKDAY(A2:A8,2)={6,7},D2:D8))
Note: Both the above fromulas are array formulas, please press Shift + Ctrl + Enter keys together to get the correct result.
https://www.extendoffice.com/images/stories/comments/comment-skyyang/2023-comment/doc-average-workdays.png

Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
How do I get my formula to work with it not taking zeros into the average?
=AVERAGE(IF(WEEKDAY($B$4:$B$34,2)={1,2,3,4,5},C4:C34))
This comment was minimized by the moderator on the site
Hello, Phil,
To get the average while skip the zeros, the following formula may help you:
=AVERAGE(IF((WEEKDAY($B$2:$B$14,2)<6)*($C$2:$C$14<>0)=1,$C$2:$C$14))

After pasting the formula, please press Ctrl + Shift + Enter keys together to get the result.

Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
Yes, this works. Why does it work with such a large RANGE and not just for the number of days in a month?

Doing it my other way I had to manually change the formula's each month. THANK YOU 😀👊
This comment was minimized by the moderator on the site
I have this formula; {=AVERAGE(IF(WEEKDAY(I12:I25,2)={1,2,3,4,5},J12:J25))} the range: i12:i25(col 1 is the month date series) and j12:j25(col of values to be averaged) will change each day
11/1/2022 57
11/2/2022 45
11/3/2022 58
11/4/2022 51
11/5/2022 56
11/6/2022 65
11/7/2022 79
11/8/2022 80
11/9/2022 56
11/10/2022 60
11/11/2022 51
11/12/2022 59
11/13/2022 79
11/14/2022 76
11/15/2022 76
11/16/2022
11/17/2022
11/18/2022
11/19/2022
11/20/2022
11/21/2022
11/22/2022
11/23/2022
11/24/2022
11/25/2022
11/26/2022
11/27/2022
11/28/2022
11/29/2022
11/30/2022
12/1/2022
12/2/2022

Avg / M-F 61.3 < formula is here {=AVERAGE(IF(WEEKDAY(I12:I25,2)={1,2,3,4,5},J12:J25))}
avg # active each workday

how do i update the 2 ranges every day without manually changing them?
This comment was minimized by the moderator on the site
Hello, Daniel
To solve your problem, you just need to adjust the cell references large as this:
=AVERAGE(IF(WEEKDAY(I12:I100000,2)={1,2,3,4,5},J12:J100000))
Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
if I use your formula for just weekends...=AVERAGE(IF(WEEKDAY(I12:I100000,2)={6,7},J12:J100000)) the value comes up as "0.0"
This comment was minimized by the moderator on the site
Hello, Daniel
Sorry, the above formula is not correct, plrase use the below formulas:
Average weekend: =AVERAGE(IF(IF(NOT(ISBLANK(B2:B10000)),WEEKDAY(A2:A10000,2)>5,FALSE),B2:B10000,FALSE))
Average workday: =AVERAGE(IF(IF(NOT(ISBLANK(B2:B10000)),WEEKDAY(A2:A10000,2)<6,FALSE),B2:B10000,FALSE))

Note: Both the formulas are array formulas, please press Ctrl + Shift + Enter keys together to get the result.

Please try, thank you!
This comment was minimized by the moderator on the site
I have this formula; {=AVERAGE(IF(WEEKDAY(I12:I25,2)={1,2,3,4,5},J12:J25))} the range: i12:i25(col 1 is the month date series) and j12:j25(col of values to be averaged) will change each day
11/1/2022 57
11/2/2022 45
11/3/2022 58
11/4/2022 51
11/5/2022 56
11/6/2022 65
11/7/2022 79
11/8/2022 80
11/9/2022 56
11/10/2022 60
11/11/2022 51
11/12/2022 59
11/13/2022 79
11/14/2022 76
11/15/2022 76
11/16/2022
11/17/2022
11/18/2022
11/19/2022
11/20/2022
11/21/2022
11/22/2022
11/23/2022
11/24/2022
11/25/2022
11/26/2022
11/27/2022
11/28/2022
11/29/2022
11/30/2022
12/1/2022
12/2/2022

Avg / M-F 61.3 < formula is here {=AVERAGE(IF(WEEKDAY(I12:I25,2)={1,2,3,4,5},J12:J25))}
avg # active each workday

how do i update the 2 ranges every day without manually changing them?
This comment was minimized by the moderator on the site
I found what I needed, why the Cntl Shift Enter to make formula work?
This comment was minimized by the moderator on the site
How is it that in the first formula 1 is Sunday but in the second and third formula 7 is Sunday??
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations