Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum quantities per hour from inconsistent intervals on a 24hr cloc
I need to create a function that will identify and compile data for specific
24 hour periods. (In Excel 2003) It may be a two step process but if there is some way to get it all done with one function i am happy to hear it. Data is in two columns, Reading Time (over a 24hr clock, for every day in a month) and Raw Readings (which are running totals, not separate quantities). Readings are not in uniform increments from hour to hour. See sample data below: Reading Time Raw Reading 7/31/2008 23:57 583200 7/31/2008 23:27 582875 7/31/2008 23:10 582833 7/31/2008 22:37 582766 7/31/2008 22:05 582707 7/31/2008 21:32 582642 7/31/2008 21:15 582641 7/31/2008 20:58 582641 7/31/2008 20:41 582640 7/31/2008 20:24 582639 7/31/2008 20:06 582639 7/31/2008 19:49 582638 7/31/2008 19:32 582637 7/31/2008 19:15 582637 7/31/2008 18:58 582636 I need to create a sum of raw data quantities per hour, per day over the 1 month period for trending purposes that looks much like the compiled data below: Reading Time Hour Quantity 9/1/2006 1:00 1 0 9/1/2006 2:00 2 0 9/1/2006 3:00 3 325 9/1/2006 4:00 4 305 9/1/2006 5:00 5 375 9/1/2006 6:00 6 272 9/1/2006 7:00 7 43 9/1/2006 8:00 8 0 9/1/2006 9:00 9 0 9/1/2006 10:00 10 0 9/1/2006 11:00 11 0 9/1/2006 12:00 12 0 9/1/2006 13:00 13 0 9/1/2006 14:00 14 0 9/1/2006 15:00 15 0 9/1/2006 16:00 16 0 9/1/2006 17:00 17 0 9/1/2006 18:00 18 0 9/1/2006 19:00 19 0 9/1/2006 20:00 20 0 9/1/2006 21:00 21 354 9/1/2006 22:00 22 307 9/1/2006 23:00 23 235 9/2/2006 0:00 24 148 Any suggestions? do i need to go into more detail? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing an axis to show time with hour intervals | Charts and Charting in Excel | |||
Converting format of hh:mm AM/PM to only hh:mm non 24hr in same co | Excel Discussion (Misc queries) | |||
how do I round hours worked to the next half hour with a 24hr shif | Excel Worksheet Functions | |||
convert decimal numbers to a fraction of an hour for payroll hour | Excel Worksheet Functions | |||
24hr moving average | Excel Discussion (Misc queries) |