Multiple averages in large continous data field

[Guest]

I figured I'd try this once more.

In column C I have the date, in column D I have the time, and in column E I
have a measurement taken at that date/time period. There’s 25,000+ rows of
this data, so doing what I need wouldn’t be something easily done manually.

Based on the date and time, I would like to find the average of the
measurements taken per date, per shift. Day shift is 7:00 AM until 9:00 PM,
and
night shift is 9:01 PM until 6:59 AM. The night shift does not need to be
spread across dates, as for example, August 30, 9:00 PM until midnight is a
separate date than August 31, 12:01 AM until 6:59 AM.

I would like to post the results for each shift per date in column G at the
first two rows per date. There are about 200 rows per date, so where the
results would be about 200 rows apart.

Is there anyway this can be done?

Thanks in advance.

 

[Guest]

Try this,

1. Insert a new colum for shift. You can decide this with a simple if
formula checcking the date and time. With this done you have all the reading
marked as either D for day shift or N for night shift.
2. After this you can easily use SUMIF()/COUTNIF() to find the average.

hope this makes sense!!

 

[Guest]

I understand what you're saying, but unfortunately, I am not good at creating
logic formulas (if then else stuff). :-(

Would this be a if and statement?

 

[iliace]

I'm going to assume that if a measurement was taken between 0700 and
2100, it was on a day shift; if not, it was during a night shift,
since the two shifts complement each other.

Insert a new column. Let's say G. Use the header "Shift". This is
the formula in G2 (assuming row 1 is header):

=IF(AND(D2>TIMEVALUE("7:00 am"),D2<TIMEVALUE("9:00 pm")),"Day
Shift","Night Shift")

Copy this formula down all the way.

Next, create a pivot table. Use Date and Shift for row values, and
measurement for data values. Change field settings to summarize by
Average (rather than count or sum, whichever it defaults to). If you
truly want to, you can then use GetPivotData function to bring the
data back to the source worksheet, just make sure the column you do
this in is not part of the pivot data range.

 

[Guest]

That worked perfectly!

Thank you very much.