Average a range between 2 dates

[edwardj3]

Hi Forum,
Hope you can help.

I have a worksheet updating every Monday with result from the previous week using VBA and appends to the right of the last entry.

When a month is complete I am then required to report the average of the data points - each day has 24 data points for each hour so month of May (31x24) had 744 data points.

Simple enough to manually return the result but is it possible to do it via either a formula or VBA?

I can't find a solution that sums / averages multiple rows between 2 dates so my current solution sums the total for each hour row
=SUMIFS(Historic_data!$B2:$ZZ2,Historic_data!$B$1:$ZZ$1,">="&B$2,Historic_data!$B$1:$ZZ$1,"<="&B$1)
B1 and B2 are the start and end dates of a month

I then sum these totals and divide by the number of data points to return the average

My date range currently goes from 1st Apr 23 to 4th Jun and increases weekly. Example below of Historic_date table layout

Hour	01/04/2023	02/04/2023	03/04/2023
00:00​	187	238	242
01:00​	188	220	224
02:00​	186	216	215
03:00​	190	224	226
04:00​	198	222	220
05:00​	190	207	215
06:00​	192	241	229
07:00​	204	218	238
08:00​	245	224	242
09:00​	311	222	256
10:00​	348	211	263
11:00​	293	217	262
12:00​	265	213	236
13:00​	230	212	296
14:00​	230	218	237
15:00​	225	221	247
16:00​	231	248	238
17:00​	221	256	253
18:00​	220	257	268
19:00​	233	224	272
20:00​	217	215	276
21:00​	221	206	252
22:00​	226	207	274
23:00​	213	214	267

Hope you can help. If you need more info please get in touch.

Thank

 

[Fluff]

How about

=AVERAGE(FILTER(Historic_data!$B2:$ZZ25,(Historic_data!$B$1:$ZZ$1>=B$2)*(Historic_data!$B$1:$ZZ$1<=B$1)))

 

[edwardj3]

How about

=AVERAGE(FILTER(Historic_data!$B2:$ZZ25,(Historic_data!$B$1:$ZZ$1>=B$2)*(Historic_data!$B$1:$ZZ$1<=B$1)))

Briliant. The formula results matches what we've done manually.
Thank you @Fluff

 

[Fluff]

You're welcome & thanks for the feedback.