Help with formula - rolling average

[BPS]

Hi -
I am hoping someone can help me with a formula.

In the spreadsheet below, in column A is the Date and Time (going up in 15 min intervals). Column B is a flow rate at that corresponding Date/Time.

I have added another column (column C) which calculates the rolling hourly average of column B. For each 24hr period I need to return the lowest of the Rolling Average values between the hours 00:00 - 06:00. I have calculated this manually (highlighted yellow) for the purpose of showing what the correct result is.

I need to apply this formula to alot of data (>100,000 rows). Please let me know if I have missed any crucial info!

1	A	B	C	D
2	Date Time	Flow (l/s)	Rolling Average (l/s)	MNF (l/s)
3	1/01/2012 00:00	0.302
4	1/01/2012 00:15	0.229
5	1/01/2012 00:30	0.037
6	1/01/2012 00:45	0.201	0.192
7	1/01/2012 01:00	0.146	0.153
8	1/01/2012 01:15	0.339	0.181
9	1/01/2012 01:30	0.343	0.257
10	1/01/2012 01:45	0.261	0.272
11	1/01/2012 02:00	0.174	0.279
12	1/01/2012 02:15	0.146	0.231
13	1/01/2012 02:30	0.046	0.157
14	1/01/2012 02:45	0.005	0.093
15	1/01/2012 03:00	0.055	0.063
16	1/01/2012 03:15	0.174	0.070
17	1/01/2012 03:30	0.018	0.063
18	1/01/2012 03:45	0.137	0.096
19	1/01/2012 04:00	0.343	0.168
20	1/01/2012 04:15	0.348	0.211
21	1/01/2012 04:30	0.092	0.230
22	1/01/2012 04:45	0.201	0.246
23	1/01/2012 05:00	0.220	0.215
24	1/01/2012 05:15	0.105	0.155
25	1/01/2012 05:30	0.018	0.136
26	1/01/2012 05:45	0.037	0.095
27	1/01/2012 06:00		0.053	0.053
28	1/01/2012 06:15	0.041	0.032
29	1/01/2012 06:30	0.009	0.029
30	1/01/2012 06:45	0.041	0.030
31	1/01/2012 07:00	0.037	0.032
32	1/01/2012 07:15		0.029
33	1/01/2012 07:30	0.513	0.197
34	1/01/2012 07:45	0.055	0.202
35	1/01/2012 08:00	0.334	0.301
36	1/01/2012 08:15	0.055	0.239
37	1/01/2012 08:30	0.224	0.167
38	1/01/2012 08:45	0.613	0.306
39	1/01/2012 09:00	0.233	0.281
40	1/01/2012 09:15	0.069	0.285
41	1/01/2012 09:30	0.531	0.361
42	1/01/2012 09:45	0.659	0.373
43	1/01/2012 10:00	0.778	0.509
44	1/01/2012 10:15	1.012	0.745
45	1/01/2012 10:30	0.664	0.778
46	1/01/2012 10:45	0.549	0.751
47	1/01/2012 11:00	0.861	0.771
48	1/01/2012 11:15	0.549	0.656
49	1/01/2012 11:30	0.284	0.561
50	1/01/2012 11:45	0.783	0.619
51	1/01/2012 12:00	1.259	0.719
52	1/01/2012 12:15	1.154	0.870
53	1/01/2012 12:30	1.117	1.078
54	1/01/2012 12:45	0.600	1.032
55	1/01/2012 13:00	0.842	0.928
56	1/01/2012 13:15	0.980	0.885
57	1/01/2012 13:30	0.348	0.693
58	1/01/2012 13:45	0.568	0.685
59	1/01/2012 14:00	0.266	0.541
60	1/01/2012 14:15	0.307	0.372
61	1/01/2012 14:30	0.430	0.393
62	1/01/2012 14:45	0.092	0.274
63	1/01/2012 15:00	0.302	0.283
64	1/01/2012 15:15	0.247	0.268
65	1/01/2012 15:30	0.467	0.277
66	1/01/2012 15:45	0.421	0.359
67	1/01/2012 16:00	0.586	0.430
68	1/01/2012 16:15	0.467	0.485
69	1/01/2012 16:30	0.197	0.418
70	1/01/2012 16:45	0.472	0.431
71	1/01/2012 17:00	0.119	0.314
72	1/01/2012 17:15	0.476	0.316
73	1/01/2012 17:30	0.055	0.281
74	1/01/2012 17:45	0.151	0.200
75	1/01/2012 18:00	0.462	0.286
76	1/01/2012 18:15	0.046	0.179
77	1/01/2012 18:30	0.490	0.287
78	1/01/2012 18:45	0.407	0.351
79	1/01/2012 19:00	0.394	0.334
80	1/01/2012 19:15	0.476	0.442
81	1/01/2012 19:30	0.476	0.438
82	1/01/2012 19:45	0.348	0.424
83	1/01/2012 20:00	0.577	0.469
84	1/01/2012 20:15	0.252	0.413
85	1/01/2012 20:30	1.190	0.592
86	1/01/2012 20:45	0.618	0.659
87	1/01/2012 21:00	0.183	0.561
88	1/01/2012 21:15	0.531	0.631
89	1/01/2012 21:30	0.490	0.455
90	1/01/2012 21:45	0.600	0.451
91	1/01/2012 22:00	0.330	0.488
92	1/01/2012 22:15	0.485	0.476
93	1/01/2012 22:30	0.206	0.405
94	1/01/2012 22:45	0.032	0.263
95	1/01/2012 23:00	0.476	0.300
96	1/01/2012 23:15		0.238
97	1/01/2012 23:30	0.119	0.209
98	1/01/2012 23:45	0.348	0.314

<COLGROUP><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1060" width=29><COL style="WIDTH: 90pt; mso-width-source: userset; mso-width-alt: 4388" width=120><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4937" width=135><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><TBODY>
</TBODY>

 

[btomjack]

Can't you just do =MIN(C3:C27) and fill down?

 

[BPS]

Perhaps I didn't explain it very well. I have many days which I need to calculate the min rolling average for (between 00:00-06:00), so there will be a different min value for each day of the week.

 

[btomjack]

=IF(HOUR(A27)=6,MIN(C3:C27),"")

If all your data is in one column, you can fill down with that formula and you will only see the minimum value from the desired timeframe. The rest will appear as blank cells.

 

[BPS]

Hi - the minimum rolling average value could occur at any time of the day - its not a fixed time range and it could be different every day, in the attached example the lowest value was the average of B23:B26. Even though the lowest could occur at any time of the day I am only concerned with the lowest average between these 00:00 - 06:00.

The formula needs to look at any of the average values between XX/XX/XXXX 00:00 and XX/XX/XXXX 06:00 and return the lowest.

 

[btomjack]

I don't see the problem with my most recent formula. Is it not working? It was designed so that you could use the "fill down" command to copy it to all the cells below it and it will still work.

Here is how it works:
1. The IF() portion of the formula makes it so the desired value only appears next to those cells that represent 6 AM.
2. The MIN(C3:C27) portion pulls the minimum value from a 6-hour time frame. When you fill down, this range of cells will change. So cell D27 has MIN(C3:C27), but cell D28 will have MIN(C4:C28). It always pulls the lowest value in the averages column for the past 6 hours.

This means that it will pull the value you are looking for and put it next to the entry for 06:00 AM. I'm assuming you're saying it's wrong because it doesn't pop up next to the minimum value itself. I don't know how to do that, but this formula will work as a substitute until you can figure out the ideal solution.