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

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

<tbody>
</tbody>

 

[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>