How to average data based on weekday or weekend in Excel?
Supposing there are two columns in a worksheet, one contains dates, and the other contains number values as below screenshot shown, how can you average the numbers based on the weekday or weekend in Excel? In this article, I introduce some formulas to quickly solve this job.
Easily Combine multiple sheets/Workbook into one Single sheet or Workbook
|To combinne multiples sheets or workbooks into one sheet or workbook may be edious in Excel, but with the Combine function in Kutools for Excel, you can combine merge dozens of sheets/workbooks into one sheet or workbook, also, you can consolidate the sheets into one by several clicks only. Click for 60 days free trial!|
|Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.|
Here I have two formulas that can quickly average values based on weekday or weekend in another cell.
Select a cell which you will place the formula and result at, type this formula =SUM((WEEKDAY(A2:A20, 2)<6)*(B2:B20))/SUM(1*(WEEKDAY(A2:A20, 2)<6)), and press Shift + Ctrl + Enter keys simultaneously.
1. If you want to average values by weekend, you can use this formula =SUM((WEEKDAY(A2:A20, 2)>5)*(B2:B20))/SUM(1*(WEEKDAY(A2:A20, 2)>5)), and press Shift + Ctrl + Enter.
2. In the formulas, A2:A20 is the range of date cells, B2:B20 is the range of numbers you want to average.