Parse Data in two hour blocks

[wilburr]

I have data which looks like

9/3/2013 5:38:14 13
9/3/2013 5:38:35 13
9/3/2013 5:42:47 13
9/3/2013 5:46:59 14
9/3/2013 5:52:14 14
9/3/2013 5:56:26 15
9/3/2013 5:59:35 15
9/3/2013 6:00:59 14
9/3/2013 6:06:16 14
9/3/2013 6:11:08 14
9/3/2013 6:13:35 14
9/3/2013 6:16:44 14


The logs are generated daily, so the first column is irrelevant at the moment.
What I want to do is to generate an average for the third column in two hour blocks. In other words, what was the average number in the third row (C) between 4:00:00 and 6:00:00. I would want a report and do that for each two hour period.

[FDibbins]

Hi and welcome to the forum

Based on your sample data, in A2:C13, try this (this will also be your report).

Create a table of times, with 2 hour intervals...
Time
2:00
4:00
6:00
8:00
10:00
12:00
14:00
16:00
18:00
20:00
22:00
then in the next column (I used G and G for this), copy this down...
=IFERROR(AVERAGEIFS($C$2:$C$13,$B$2:$B$13,">="&F1,$B$2:$B$13,"<"&F2),"")

[wilburr]

Sorry, I guess I didn't explain that we will not know the number of entries in each two hour block. Either that or I am misreading your formula.

[MarvinP]

Hi wilburr and welcome to the forum,

I've created a sample workbook for you with random times. I included a Helper Column with an MRound formula in it to round to the closest 2 hour and then did a Pivot Table using the data that easily shows the Average of the times included in each group. I think that is what you want. See if my sample helps.

[FDibbins]

You dont need to know how many entries will be in each block, if you create the table as I suggested, and use the formula provided, it will calc the averages for each block...

	A	B	C
1
2	9/3/2013	5:38:14	13
3	9/3/2013	5:38:35	13
4	9/3/2013	5:42:47	13
5	9/3/2013	5:46:59	14
6	9/3/2013	5:52:14	14
7	9/3/2013	5:56:26	15
8	9/3/2013	5:59:35	15
9	9/3/2013	6:00:59	14
10	9/3/2013	6:06:16	14
11	9/3/2013	6:11:08	14
12	9/3/2013	6:13:35	14
13	9/3/2013	6:16:44	14

	G	H
2	2:00
3	4:00	13.85714
4	6:00	14
5	8:00
6	10:00
7	12:00
8	14:00
9	16:00
10	18:00
11	20:00
12	22:00

[wilburr]

FDibbins

Would you send me a copy of the file with your working formula? I pasted you formula into the spreadsheet and saw no output and did not receive an error message.

[FDibbins]

sure, see the attached There will not be an error message, because part of the formula takes care of errors

[FDibbins]

self-deleted duplicate post

[wilburr]

I see how this works now, but I have a problem. I use the formula. It calculates the value. Everything looks good. I close the spreadsheet and reopen it later. Everything still looks good but the formula is gone.

I have gone back and checked. The formula is still there when the results are returned, but not after I close and reopen the file. Thoughts?

[FDibbins]

wow that is wierd, all I can think of is that you didnt save the file before you closed it?

[wilburr]

No, the results remain, just the formula is gone. Not just yours, simple average formulas show the same pattern.

[FDibbins]

Any chance of seeing the actual workbook? If it contains sensitive info, dont post here

[wilburr]

I think it is attached. I am new to the forum.

[wilburr]

I think I found the problem. The data comes in as a csv. The formula works in the csv, but not when saved as a csv. Now I add the formula, save as excell and the formula sticks.

Thanks for all of your help!

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.