Averageifs help with large data range

[vasili1991]

hi all

i need some help finding averages with unique criteria range.

i have to work out the average value between 7:00 - 10:00 and 15:00 - 19:00 for everyday of august

the data usually comes out in 5 min increments but sometimes its not but i still need it included with closest too value

heres how the data looks when i get it

any help would be appreciated thanks
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 129"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Time Period Ending[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 5:25[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 5:50[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 6:05[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 6:10[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 6:20[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 6:25[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 6:30[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 6:40[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 6:45[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 6:50[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 6:55[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 7:00[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 7:10[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 7:20[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 7:25[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 7:35[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 7:40[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 7:45[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 7:50[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 7:55[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 8:00[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 8:05[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 8:10[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 8:15[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 8:20[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 8:25[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 8:30[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 8:35[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 8:40[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 8:45[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 8:50[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 8:55[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 9:00[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 9:05[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 9:10[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 9:20[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 9:25[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 9:30[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 9:35[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 9:40[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 9:45[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 9:50[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 9:55[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 10:00[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 10:05[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 10:10[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 10:15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Y-Value[/TD]
[/TR]
[TR]
[TD="align: right"]0.920833333[/TD]
[/TR]
[TR]
[TD="align: right"]0.9[/TD]
[/TR]
[TR]
[TD="align: right"]1.354166667[/TD]
[/TR]
[TR]
[TD="align: right"]1.383333333[/TD]
[/TR]
[TR]
[TD="align: right"]1.004166667[/TD]
[/TR]
[TR]
[TD="align: right"]0.94375[/TD]
[/TR]
[TR]
[TD="align: right"]1.042857143[/TD]
[/TR]
[TR]
[TD="align: right"]1.219444444[/TD]
[/TR]
[TR]
[TD="align: right"]0.995833333[/TD]
[/TR]
[TR]
[TD="align: right"]1.70952381[/TD]
[/TR]
[TR]
[TD="align: right"]1.704761905[/TD]
[/TR]
[TR]
[TD="align: right"]0.995[/TD]
[/TR]
[TR]
[TD="align: right"]1.274074074[/TD]
[/TR]
[TR]
[TD="align: right"]1.275[/TD]
[/TR]
[TR]
[TD="align: right"]1.155[/TD]
[/TR]
[TR]
[TD="align: right"]1.257142857[/TD]
[/TR]
[TR]
[TD="align: right"]1.281481481[/TD]
[/TR]
[TR]
[TD="align: right"]1.474358974[/TD]
[/TR]
[TR]
[TD="align: right"]1.257894737[/TD]
[/TR]
[TR]
[TD="align: right"]1.479487179[/TD]
[/TR]
[TR]
[TD="align: right"]1.614814815[/TD]
[/TR]
[TR]
[TD="align: right"]1.975[/TD]
[/TR]
[TR]
[TD="align: right"]1.870512821[/TD]
[/TR]
[TR]
[TD="align: right"]1.388888889[/TD]
[/TR]
[TR]
[TD="align: right"]1.58125[/TD]
[/TR]
[TR]
[TD="align: right"]2.55[/TD]
[/TR]
[TR]
[TD="align: right"]2.14[/TD]
[/TR]
[TR]
[TD="align: right"]2.032051282[/TD]
[/TR]
[TR]
[TD="align: right"]2.169607843[/TD]
[/TR]
[TR]
[TD="align: right"]2.047777778[/TD]
[/TR]
[TR]
[TD="align: right"]1.618518519[/TD]
[/TR]
[TR]
[TD="align: right"]1.851388889[/TD]
[/TR]
[TR]
[TD="align: right"]1.379166667[/TD]
[/TR]
[TR]
[TD="align: right"]2.285897436[/TD]
[/TR]
[TR]
[TD="align: right"]1.394444444[/TD]
[/TR]
[TR]
[TD="align: right"]2.631481481[/TD]
[/TR]
[TR]
[TD="align: right"]1.641666667[/TD]
[/TR]
[TR]
[TD="align: right"]1.924074074[/TD]
[/TR]
[TR]
[TD="align: right"]1.565[/TD]
[/TR]
[TR]
[TD="align: right"]2.254545455[/TD]
[/TR]
[TR]
[TD="align: right"]1.205882353[/TD]
[/TR]
[TR]
[TD="align: right"]1.633333333[/TD]
[/TR]
[TR]
[TD="align: right"]1.626666667[/TD]
[/TR]
[TR]
[TD="align: right"]1.055555556[/TD]
[/TR]
[TR]
[TD="align: right"]0.975[/TD]
[/TR]
[TR]
[TD="align: right"]1.206666667[/TD]
[/TR]
[TR]
[TD="align: right"]1.146666667[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]here below is more data but not exactly 5 min increments [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 129"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]1 Aug 17 14:50[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 14:55[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 15:20[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 15:30[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 15:35[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 15:40[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 15:50[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 15:55[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 16:00[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 16:20[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 16:25[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 16:30[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 16:35[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 17:25[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 17:35[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 17:40[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 18:10[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 18:40[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 20:10[/TD]
[/TR]
[TR]
[TD="align: right"]1 Aug 17 20:15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2.041666667[/TD]
[/TR]
[TR]
[TD="align: right"]2.3[/TD]
[/TR]
[TR]
[TD="align: right"]1.291666667[/TD]
[/TR]
[TR]
[TD="align: right"]1.986666667[/TD]
[/TR]
[TR]
[TD="align: right"]1.755555556[/TD]
[/TR]
[TR]
[TD="align: right"]1.702380952[/TD]
[/TR]
[TR]
[TD="align: right"]1.875[/TD]
[/TR]
[TR]
[TD="align: right"]2.55[/TD]
[/TR]
[TR]
[TD="align: right"]1.844444444[/TD]
[/TR]
[TR]
[TD="align: right"]2.076666667[/TD]
[/TR]
[TR]
[TD="align: right"]1.80625[/TD]
[/TR]
[TR]
[TD="align: right"]1.445833333[/TD]
[/TR]
[TR]
[TD="align: right"]1.291666667[/TD]
[/TR]
[TR]
[TD="align: right"]2.595833333[/TD]
[/TR]
[TR]
[TD="align: right"]4.233333333[/TD]
[/TR]
[TR]
[TD="align: right"]2.694444444[/TD]
[/TR]
[TR]
[TD="align: right"]1.353333333[/TD]
[/TR]
[TR]
[TD="align: right"]1.576666667[/TD]
[/TR]
[TR]
[TD="align: right"]1.2375[/TD]
[/TR]
[TR]
[TD="align: right"]1.586111111[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[barry houdini]

Hello vasili1991, welcome to MrExcel

I don't think you can use AVERAGEIFS for this - try using an array formula with AVERAGE function, e.g. with date/time values in A2:A1000, values in B2:B1000 and a list of dates to average for in D2 down, use this array formula in E2

=AVERAGE(IF(INT(A$2:A$1000)=D2,IF((MOD(A$2:A$1000,1)>=7/24)*(MOD(A$2:A$1000,1)<=10/24)+(MOD(A$2:A$1000,1)>=15/24)*( MOD(A$2:A$1000,1)<=19/24),B$2:B$1000)))

confirm with CTRL+SHIFT+ENTER and copy down
​

​

<strike></strike>