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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum quantities per hour from inconsistent intervals on a 24hr cloc
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) -- Biff Microsoft Excel MVP "E" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum quantities per hour from inconsistent intervals on a 24hr cloc
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum quantities per hour from inconsistent intervals on a 24hr cloc
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" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum quantities per hour from inconsistent intervals on a 24hr
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 "Ron Rosenfeld" wrote: 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 |
Reply |
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) |