Daily Averages

[searun13]

Good Day!
I have a ton of 15 minute flow data (3rd column), organized by date/time in one column, and the the flow in another column. It is over a years worth of data. The number of measurements per day vary slightly, so there is not an exact number taken every day, which is making it difficult when calculating daily averages over the entire year. Is there a formula that I could use that would calculate these averages and put them in a column by day with out the 96 or so spaced gaps? Here is an example of the data. Let me know if I should attach a file.

12/10/10 12:03 AM 1.33 40.32
12/10/10 12:18 AM 1.33 39.73
12/10/10 12:34 AM 1.33 40.32
12/10/10 12:49 AM 1.32 40.11
12/10/10 1:05 AM 1.33 40.32
12/10/10 1:20 AM 1.32 39.91
12/10/10 1:36 AM 1.33 40.32
12/10/10 1:51 AM 1.32 40.11
12/10/10 2:07 AM 1.32 39.91
12/10/10 2:22 AM 1.32 39.91
12/10/10 2:38 AM 1.32 39.70
12/10/10 2:53 AM 1.32 39.70
12/10/10 3:10 AM 1.32 39.70
12/10/10 3:25 AM 1.32 39.49
12/10/10 3:41 AM 1.32 39.49
12/10/10 3:56 AM 1.31 39.29
12/10/10 4:12 AM 1.31 39.29
12/10/10 4:27 AM 1.31 39.08
12/10/10 4:43 AM 1.31 39.29
12/10/10 4:58 AM 1.31 38.88
12/10/10 5:15 AM 1.31 39.08
12/10/10 5:30 AM 1.31 39.08
12/10/10 5:46 AM 1.31 38.88
12/10/10 6:01 AM 1.31 39.08
12/10/10 6:17 AM 1.30 38.67
12/10/10 6:32 AM 1.30 38.67
12/10/10 6:48 AM 1.30 38.67
12/10/10 7:03 AM 1.30 38.67
12/10/10 7:19 AM 1.30 38.67
12/10/10 7:34 AM 1.30 38.67
12/10/10 7:51 AM 1.30 38.67
12/10/10 8:06 AM 1.30 38.67
12/10/10 8:21 AM 1.30 38.46
12/10/10 8:36 AM 1.30 38.67
12/10/10 8:52 AM 1.30 38.26
12/10/10 9:07 AM 1.30 38.46
12/10/10 9:23 AM 1.30 38.46
12/10/10 9:38 AM 1.30 38.46
12/10/10 9:54 AM 1.30 38.46
12/10/10 10:09 AM 1.30 38.46
12/10/10 10:24 AM 1.30 38.46
12/10/10 10:39 AM 1.30 38.26
12/10/10 10:55 AM 1.30 38.26
12/10/10 11:10 AM 1.30 38.26
12/10/10 11:26 AM 1.30 38.26
12/10/10 11:41 AM 1.30 38.26
12/10/10 11:58 AM 1.30 38.26
12/10/10 12:13 PM 1.30 38.26
12/10/10 12:29 PM 1.29 38.05
12/10/10 12:44 PM 1.29 38.05
12/10/10 1:00 PM 1.29 38.05
12/10/10 1:15 PM 1.29 38.05
12/10/10 1:32 PM 1.29 38.05
12/10/10 1:47 PM 1.30 38.26
12/10/10 2:02 PM 1.30 38.26
12/10/10 2:18 PM 1.30 38.26
12/10/10 2:33 PM 1.29 37.85
12/10/10 2:48 PM 1.29 37.85
12/10/10 3:04 PM 1.28 37.43
12/10/10 3:19 PM 1.29 37.85
12/10/10 3:34 PM 1.29 37.85
12/10/10 3:49 PM 1.29 37.64
12/10/10 4:05 PM 1.29 37.64
12/10/10 4:20 PM 1.28 37.43
12/10/10 4:35 PM 1.28 37.23
12/10/10 4:50 PM 1.28 37.43
12/10/10 5:06 PM 1.28 37.43
12/10/10 5:21 PM 1.28 37.43
12/10/10 5:36 PM 1.28 37.43
12/10/10 5:51 PM 1.28 37.43
12/10/10 6:07 PM 1.28 37.43
12/10/10 6:22 PM 1.28 37.23
12/10/10 6:37 PM 1.28 37.43
12/10/10 6:52 PM 1.28 37.23
12/10/10 7:08 PM 1.28 37.23
12/10/10 7:23 PM 1.28 37.02
12/10/10 7:38 PM 1.28 37.02
12/10/10 7:53 PM 1.28 37.02
12/10/10 8:08 PM 1.27 36.82
12/10/10 8:23 PM 1.28 37.23
12/10/10 8:39 PM 1.28 37.02
12/10/10 8:54 PM 1.28 37.02
12/10/10 9:09 PM 1.27 36.82
12/10/10 9:24 PM 1.27 36.82
12/10/10 9:40 PM 1.28 37.02
12/10/10 9:55 PM 1.28 37.02
12/10/10 10:11 PM 1.28 37.02
12/10/10 10:26 PM 1.28 37.02
12/10/10 10:42 PM 1.28 37.02
12/10/10 10:57 PM 1.27 36.82
12/10/10 11:12 PM 1.28 37.02
12/10/10 11:27 PM 1.28 37.02
12/10/10 11:43 PM 1.27 36.82
12/10/10 11:58 PM 1.27 36.61

[Pete_UK]

Yeah, perhaps it would be better to attach a sample file - it's not clear if your date is in one column with the time next to it or if they occupy one column. Also, it's not clear what the other two columns are - which one do you want to average?

Pete

[searun13]

Pete,
The time and date are in the same column, I am trying to get daily averages for the last set of numbers (larger of the two sets).

[searun13]

I have attached the file if your interest is still piqued.

HydroTest1.xlsx

[FDibbins]

Create a table with just dates (I used E2 down), and then use this, copied down...
=AVERAGEIFS($B:$B,$A:$A,">="&E2,$A:$A,"<"&E3)

[searun13]

FDibbins,
I appreciate your help, but I am not sure on the specifics. Do you means a table with just dates, so one cell for each particular day of the month in column A with data next to it in column B with the formula starting on cell E2?

[FDibbins]

yes exactly. How else would you show all the daily averages, unless tou have a table showing all the dates?

[searun13]

Hydro2test.xlsx

FDibbins,
I am having an issue getting your AverageIF statement to calculated correctly, not sure what I am doing wrong. I attached my attempt at how you mentioned I should try to calculate daily average values.

[searun13]

Hydro2test.xlsx

FDibbins,
I attached the wrong file, please see this one as opposed to the one in the last reply. Thanks.

[FDibbins]

1st, that file only has unique dates in it, and no times
2nd, you need to create the date table 1st, showing each date (as mentioned above, I used E for this)
3rd, then you need to copy the above formula down in the column NEXT to where you put the dates, to pull out the averages

[searun13]

Thank you sir, worked like a charm. You have saved me much time and helped me learn in the process.

[FDibbins]

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)