Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A list of data with time stamps, how subtotal at 10 min intervals
In a list of data with time stamps for each data item, how can excel subtotal
the data items at 10 minute or hourly intervals. The time stamps are random so that the number of data points within each 10 minute interval may vary. Thanks for your help. John |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A list of data with time stamps, how subtotal at 10 min intervals
When you say "subtotal" do you mean a count of the number of items
within each time interval, or is there some other field that you want to add up? Which column contains the time-stamps, and which column needs to subtotalled? How many records do you have (typically)? Do you want a separate table of time intervals and number (and if so is this to go into another sheet)? Pete On Sep 18, 9:24 pm, John wrote: In a list of data with time stamps for each data item, how can excel subtotal the data items at 10 minute or hourly intervals. The time stamps are random so that the number of data points within each 10 minute interval may vary. Thanks for your help. John |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A list of data with time stamps, how subtotal at 10 min interv
Pete:
Thanks for your interest. The data from the instrument indicates a time stamp in one column and the data in a second column. At each 10 minute interval I want to calculate an average of the data points during that 10 minute interval. The number of data points within a 10 minute interval will vary. The column layout can be manipulated in excel, if that make a difference. So I want to sort the data in time stamp sequence, then average the data in the data column at each 10 intervals of the time stamps. I would also like to do the same at the hour mark. Your input would be appreciated. -- John "Pete_UK" wrote: When you say "subtotal" do you mean a count of the number of items within each time interval, or is there some other field that you want to add up? Which column contains the time-stamps, and which column needs to subtotalled? How many records do you have (typically)? Do you want a separate table of time intervals and number (and if so is this to go into another sheet)? Pete On Sep 18, 9:24 pm, John wrote: In a list of data with time stamps for each data item, how can excel subtotal the data items at 10 minute or hourly intervals. The time stamps are random so that the number of data points within each 10 minute interval may vary. Thanks for your help. John |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A list of data with time stamps, how subtotal at 10 min interv
Hi John,
If you are prepared to sort your data then you could make use of Data | Subtotals - you would need a helper column to convert the time into the appropriate time interval (at 10 minutes or 1 hour increments) and use this as the change to trigger the subtotal. However, the method I describe below does not interfere with the data from your instrument, and will give you the results automatically in separate tables. I have assumed that your data is in Sheet1 and has times in Excel format in column A and the numbers you want to average in column B, beginning with row 1 (no headers). In my tests I set up data in 100 rows, so adjust this to suit the size of your data. First of all, insert a new sheet and put these headings in the appropriate cells: A1: 10min Interval B1: Number C1: Total D1: Average F1: Hourly Interval G1: Number H1: Total I1: Average In cell A2 you could enter 0:00 if your times can start that early and put this formula in A3: =A2+10/60/24 Format both cells using a custom format of [hh]:mm and copy the formula in A3 down until you get to 24:00. Alternatively, put this formula in A2: =FLOOR(MIN(Sheet1!A1:A100)*24*60,10)/60/24 This will find the earliest time in your data set and choose a starting increment automatically. You will still need the formula in A3, copied down. Then in B2 put this formula: =SUMPRODUCT((Sheet1!A$1:A$100=A2)*(Sheet1!A$1:A$1 00<A3)) and copy this down (double-click the fill icon, i.e. the small black square in the bottom right corner of the cursor). This will give you a count of the number of items in each time interval. You can then enter this formula in C2: =SUMPRODUCT((Sheet1!A$1:A$100=A2)*(Sheet1!A$1:A$1 00<A3)*(Sheet1!B$1:B $100)) and copy this down by double-clicking the fill icon. This will total the values in column B of your data which fall into the time interval. Finally, put this formula in D2: =IF(B2=0,0,C2/B2) and copy this down to get your average. Of course, you could combine the two formulae above to get the average directly, but the composite formula will be a bit horrendous. You can do a similar thing in the other table, but for 1-hour increments. Here's the relevant formulae: F3: =F2+1/24 G2: =SUMPRODUCT((Sheet1!A$1:A$100=F2)*(Sheet1!A$1:A$1 00<F3)) H2: =SUMPRODUCT((Sheet1!A$1:A$100=F2)*(Sheet1!A$1:A$1 00<F3)*(Sheet1! B$1:B$100)) I2: =IF(G2=0,0,H2/G2) and in F2 you can either start with 0:00 or with: =FLOOR(MIN(Sheet1!A1:A100)*24,1)/24 to give you the start increment automatically. Obviously, you will not need to copy these formual down as many rows. Be sure to change 100 to suit your data (doesn't matter if it is much larger), and then just paste your data into Sheet1 each day and get your results on Sheet2. Hope this helps. Pete On Sep 19, 2:42 am, John wrote: Pete: Thanks for your interest. Thedatafrom the instrument indicates atime stamp in one column and thedatain a second column. At each 10 minute interval I want to calculate an average of thedatapoints during that 10 minute interval. The number ofdatapoints within a 10 minute interval will vary. The column layout can be manipulated in excel, if that make a difference. So I want to sort thedataintimestamp sequence, then average thedatain thedatacolumn at each 10 intervals of thetimestamps. I would also like to do the same at the hour mark. Your input would be appreciated. -- John "Pete_UK" wrote: When you say "subtotal" do you mean a count of the number of items within eachtimeinterval, or is there some other field that you want to add up? Which column contains thetime-stamps, and which column needs to subtotalled? How many records do you have (typically)? Do you want a separate table oftimeintervals and number (and if so is this to go into another sheet)? Pete On Sep 18, 9:24 pm, John wrote: In alistofdatawithtimestampsfor eachdataitem, how can excel subtotal thedataitems at 10 minute or hourly intervals. Thetimestampsare random so that the number ofdatapoints within each 10 minute interval may vary. Thanks for your help. John- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A list of data with time stamps, how subtotal at 10 min interv
Thanks for feeding back, John - glad you are able to make use of it.
Pete On Sep 21, 1:26 am, John wrote: Pete: Excellent suggestion. Quite clever. Thanks, I am going to use it Regards, -- J "Pete_UK" wrote: Hi John, If you are prepared to sort your data then you could make use of Data | Subtotals - you would need a helper column to convert the time into the appropriate time interval (at 10 minutes or 1 hour increments) and use this as the change to trigger the subtotal. However, the method I describe below does not interfere with the data from your instrument, and will give you the results automatically in separate tables. I have assumed that your data is in Sheet1 and has times in Excel format in column A and the numbers you want to average in column B, beginning with row 1 (no headers). In my tests I set up data in 100 rows, so adjust this to suit the size of your data. First of all, insert a new sheet and put these headings in the appropriate cells: A1: 10min Interval B1: Number C1: Total D1: Average F1: Hourly Interval G1: Number H1: Total I1: Average In cell A2 you could enter 0:00 if your times can start that early and put this formula in A3: =A2+10/60/24 Format both cells using a custom format of [hh]:mm and copy the formula in A3 down until you get to 24:00. Alternatively, put this formula in A2: =FLOOR(MIN(Sheet1!A1:A100)*24*60,10)/60/24 This will find the earliest time in your data set and choose a starting increment automatically. You will still need the formula in A3, copied down. Then in B2 put this formula: =SUMPRODUCT((Sheet1!A$1:A$100=A2)*(Sheet1!A$1:A$1 00<A3)) and copy this down (double-click the fill icon, i.e. the small black square in the bottom right corner of the cursor). This will give you a count of the number of items in each time interval. You can then enter this formula in C2: =SUMPRODUCT((Sheet1!A$1:A$100=A2)*(Sheet1!A$1:A$1 00<A3)*(Sheet1!B$1:B $100)) and copy this down by double-clicking the fill icon. This will total the values in column B of your data which fall into the time interval. Finally, put this formula in D2: =IF(B2=0,0,C2/B2) and copy this down to get your average. Of course, you could combine the two formulae above to get the average directly, but the composite formula will be a bit horrendous. You can do a similar thing in the other table, but for 1-hour increments. Here's the relevant formulae: F3: =F2+1/24 G2: =SUMPRODUCT((Sheet1!A$1:A$100=F2)*(Sheet1!A$1:A$1 00<F3)) H2: =SUMPRODUCT((Sheet1!A$1:A$100=F2)*(Sheet1!A$1:A$1 00<F3)*(Sheet1! B$1:B$100)) I2: =IF(G2=0,0,H2/G2) and in F2 you can either start with 0:00 or with: =FLOOR(MIN(Sheet1!A1:A100)*24,1)/24 to give you the start increment automatically. Obviously, you will not need to copy these formual down as many rows. Be sure to change 100 to suit your data (doesn't matter if it is much larger), and then just paste your data into Sheet1 each day and get your results on Sheet2. Hope this helps. Pete On Sep 19, 2:42 am, John wrote: Pete: Thanks for your interest. Thedatafrom the instrument indicates atime stamp in one column and thedatain a second column. At each 10 minute interval I want to calculate an average of thedatapoints during that 10 minute interval. The number ofdatapoints within a 10 minute interval will vary. The column layout can be manipulated in excel, if that make a difference. So I want to sort thedataintimestamp sequence, then average thedatain thedatacolumn at each 10 intervals of thetimestamps. I would also like to do the same at the hour mark. Your input would be appreciated. -- John "Pete_UK" wrote: When you say "subtotal" do you mean a count of the number of items within eachtimeinterval, or is there some other field that you want to add up? Which column contains thetime-stamps, and which column needs to subtotalled? How many records do you have (typically)? Do you want a separate table oftimeintervals and number (and if so is this to go into another sheet)? Pete On Sep 18, 9:24 pm, John wrote: In alistofdatawithtimestampsfor eachdataitem, how can excel subtotal thedataitems at 10 minute or hourly intervals. Thetimestampsare random so that the number ofdatapoints within each 10 minute interval may vary. Thanks for your help. John- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Stamps??????????? | Excel Discussion (Misc queries) | |||
Large Quantity of Data, Graphed in Time Intervals | Charts and Charting in Excel | |||
Sorting data with time stamps | Excel Discussion (Misc queries) | |||
I need to find the yy/mm/dd AND hh:mm:ss between 2 time stamps | Excel Discussion (Misc queries) | |||
Add same set of formulae at defined intervals to list of row data | Excel Worksheet Functions |