Spreadsheet: 858988d1707471963-daywise-average-for-the-week-from-shift-wise-data-daywise-average
Sheet: Sheet1

 |  |  | 2024-01-01 00:00:00
 |  |  | A | B | C | A | B | C | A | B | C | A | B | C | A | B | C | A | B | C | A | B | C | A | B | C | A | B | C | A | B | C | A | B | C | A | B | C | A | B | C | A | B | C | A | B | C | A | B | C | A | B | C | A | B | C | A | B | C | A | B | C
 |  |  | 1 | 2 | 3 | 2 | 3 | 4 | 3 | 4 | 5 | 2 | 2 | 2 | 1 | 1 | 1 | 2 | 3 | 4 | 2 | 2 | 2 | 3 | 2 | 1 | 2 | 3 | 2 | 3 | 3 | 3 | 4 | 4 | 4 | 4 | 1 | 3 | 2 | 1 | 3 | 4 | 5 | 1 | 1 | 1 |  | 5 | 1 |  | 3 | 3 |  | 1 | 3 |  | 1 | 1 |  | 1 | 3
Week No. | Date Range
1 | 1-6 | 7.5
2 | 8-13 | 8 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Holidays & Sundays:
3 | 15-20 | 4 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-01-07 00:00:00
4 | 22-27 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-01-14 00:00:00
5 | 29-31 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-01-21 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-01-28 00:00:00
Need formula in Yellow highlighted cells to calculate Day Average for the week (Sum day-wise and then average) |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-02-04 00:00:00
Holidays and (Sundays) (Col.R) are to be excluded while calculating average. |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-02-11 00:00:00
Solution data is put manually for ready reference. |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-02-18 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-02-25 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-03-03 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-03-10 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-03-17 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-03-24 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-03-31 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-04-07 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-04-14 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-04-21 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-04-28 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-05-05 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-05-12 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-05-19 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-05-26 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-06-02 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-06-09 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-06-16 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-06-23 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-06-30 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-07-07 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-07-14 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-07-21 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-07-28 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-08-04 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-08-11 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-08-18 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-08-25 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-09-01 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-09-08 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-09-15 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-09-22 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-09-29 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-10-06 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-10-13 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-10-20 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-10-27 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-11-03 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-11-10 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-11-17 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-11-24 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-12-01 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-12-08 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-12-15 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-12-22 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-12-29 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-01-01 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-01-15 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-01-26 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-04-11 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-05-01 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-08-15 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-09-07 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-10-02 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-10-12 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-12-25 00:00:00