Hi
I need a formula to average some data based on a date range but excluding weekends, a simplified data set would be
What I want is the average of the count column between the dates 18/01/21 and 25/01/21 but excluding the 23rd and 24th - the date range (ie 18th - 25th) is defined by input into cells F2 & F3 so the formula needs to cover the whole range
I've tried AVERAGEIFS which works for the date range
=AVERAGEIFS(B2:B12,A2:A12,">="&F2,A2:A12,"<="&F3)
but when I tried to exclude dates by incorporating a WEEKDAY element it fails
Any suggestion on how I achieve my goal would be much appreciated
I need a formula to average some data based on a date range but excluding weekends, a simplified data set would be
| Date | Count |
16/01/2021 | 1 |
17/01/2021 | 2 |
18/01/2021 | 3 |
19/01/2021 | 4 |
20/01/2021 | 5 |
21/01/2021 | 6 |
22/01/2021 | 7 |
23/01/2021 | 8 |
24/01/2021 | 9 |
25/01/2021 | 10 |
26/01/2021 | 11 |
What I want is the average of the count column between the dates 18/01/21 and 25/01/21 but excluding the 23rd and 24th - the date range (ie 18th - 25th) is defined by input into cells F2 & F3 so the formula needs to cover the whole range
I've tried AVERAGEIFS which works for the date range
=AVERAGEIFS(B2:B12,A2:A12,">="&F2,A2:A12,"<="&F3)
but when I tried to exclude dates by incorporating a WEEKDAY element it fails
Any suggestion on how I achieve my goal would be much appreciated
Last edited:
