Sum quantities per hour from inconsistent intervals on a 24hr cloc

[E]

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?

 

[T. Valko]

In other words...

Take the MAX reading for the date/hour and subtract the MIN reading for the
date/hour?

Well, it would have been easier to figure out if the sample results you
posted were based on the sample data you posted!

Based on the sample data...

A2:An = reading date/time
B2:Bn = reading

D2 = 7/31/2008 23:00
E2 = 23

Enter this array formula** in F2:

=MAX(IF((INT(A2:A16)=INT(D2))*(HOUR(A2:A16)=E2),B2:B16))-MIN(IF((INT(A2:A16)=INT(D2))*(HOUR(A2:A16)=E2),B2:B16))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Ron Rosenfeld]

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

 

[Shane Devenshire]

Hi,

This is a little confusing since your data at the top is for July and your
result area if for September?
Since it appears that your date/time are in Descending order you can use the
following Array formula

=IF((INT(A1:A15)=D1)*(HOUR(A1:A15)=E1),B1:B15)-MIN(IF((INT(A1:A15)=D1)*(HOUR(A1:A15)=E1),B1:B15))

This assumes that the time readings start in A1 and the Raw readings in B1.
enter the date in D1 and the time in hours (24 hour clock) in cell E1. In
this example I entered 7/31/2008 in D1 and 23 in E1. Array formulas are
entered by pressing Shift+Ctrl+Enter.

Since the same ranges come up multiple times in the formula you could name
A1:A15 A (for column A) and B1:B15 B (for column B), then your formula
would simplify to

=IF((INT(A)=D1)*(HOUR(A)=E1),B)-MIN(IF((INT(A)=D1)*(HOUR(A)=E1),B))

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[E]

the values you have come up with are what i am looking for, but i am
struggling to replicate the pivot table on my own.
you have designated date/time for the row values and raw reading for the
data section, but no columns?
also if you could clarify how you consolidated the rows by hour and date, as
this would prove to be a vital part of this data set (there are over 2600
data reading for the month of July and the pivot table can only handle 500 at
a time)
or would this rather be a question best suited for another forum?
thank you again for your help