A list of data with time stamps, how subtotal at 10 min intervals

[John]

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

 

[Pete_UK]

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

 

[John]

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.

 

[Pete_UK]

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$100<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$100<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$100<F3))
H2: =SUMPRODUCT((Sheet1!A$1:A$100>=F2)*(Sheet1!A$1:A$100<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

 

[John]

Pete:

Excellent suggestion. Quite clever. Thanks, I am going to use it

Regards,
--
J

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$100<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$100<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$100<F3))
H2: =SUMPRODUCT((Sheet1!A$1:A$100>=F2)*(Sheet1!A$1:A$100<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

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







- Show quoted text -

 

[Pete_UK]

Thanks for feeding back, John - glad you are able to make use of it.

Pete

Pete:

Excellent suggestion. Quite clever. Thanks, I am going to use it

Regards,
--
J

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$100<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$100<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$100<F3))
H2: =SUMPRODUCT((Sheet1!A$1:A$100>=F2)*(Sheet1!A$1:A$100<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:

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.

- Show quoted text -