On Mon, 29 Dec 2008 17:24:02 -0800, E wrote:

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?


It's not really clear how you are doing the computation.

Since these are raw readings, I suppose we could take the Max reading for a
given hour and subtract from that the Max reading during the preceding hour.

Of course, this might not be accurate if there is a long delay between readings
that spans the hour marker.

But I used a Pivot table. I dragged the Date/Time to the Rows area; and the
Raw readings to the Value (or Data) area. Then I consolidated the rows by Hour
and Date; for the Values, I chose Max; and display as difference from Previous.

This was the result. You'll have to figure out whether it's what you want,
since the results you posted were not from the time period for which you posted
data

============================
Date / Hr Data Quantity Per Hour
31-Jul
6 PM
7 PM 2
8 PM 3
9 PM 1
10 PM 124
11 PM 434
============================
--ron