Rolling Average, Time

[Guest]

I would like to know the best method to average time. I would ideally like to
keep the time in seconds.tenths [sss.0]. Additionally I would like to keep a
rolling average of the compiled results. So that as new results are added
they will be calculated with the old results, and produce a possible new
average, to be saved until the next set of results are added. Is this
possible? And can this be somewhat automated?

 

[Guest]

If you are only recording seconds and tenths then you will only need to enter
normal numbers i.e.45.6, 52.4 etc. In the next column you could enter a
formula such as =SUM($A$1:A1)/COUNT($A$1:A1) then copy it down. The $A$1
will remain static so the sum will include the next cell in the column and
you will be dividing by the number of cells which will also increment down
the column.

 

[David Biddulph]

Yes. If you've got cells in Excel time format, format the cells as .0 if
you want to display the time as seconds and tenths. Otherwise, of course,
you could just work in seconds, rather than in time, and format the cells as
number with one decimal place.
In row 2 put the formula
=IF(A2="","",AVERAGE(A$1:A2)), and copy down.
This will give you an average from the first row to the latest one as it's
added, leaving each average cell blank until the corresponding row is
completed.
If you want only the latest average shown, you could change the row 2
formula to
=IF(A3="",IF(A2="","",AVERAGE(A$1:A2)),"")

 

[Guest]

Both of these solutions work well. evidently what they say about the skinning
of a cat is true. Perhaps I could push my luck, and ask for a way to control
where the results would end up. Instead of being at the bottom of what could
be a very long column, could the updated answer be at the top? Or elsewhere?

Yes. If you've got cells in Excel time format, format the cells as .0 if
you want to display the time as seconds and tenths. Otherwise, of course,
you could just work in seconds, rather than in time, and format the cells as
number with one decimal place.
In row 2 put the formula
=IF(A2="","",AVERAGE(A$1:A2)), and copy down.
This will give you an average from the first row to the latest one as it's
added, leaving each average cell blank until the corresponding row is
completed.
If you want only the latest average shown, you could change the row 2
formula to
=IF(A3="",IF(A2="","",AVERAGE(A$1:A2)),"")
--
David Biddulph

I would like to know the best method to average time. I would ideally like
to
keep the time in seconds.tenths [sss.0]. Additionally I would like to keep
a
rolling average of the compiled results. So that as new results are added
they will be calculated with the old results, and produce a possible new
average, to be saved until the next set of results are added. Is this
possible? And can this be somewhat automated?

 

[David Biddulph]

Yes, you can use =AVERAGE(A:A), providing you don't put that in column A.
--
David Biddulph

Both of these solutions work well. evidently what they say about the
skinning
of a cat is true. Perhaps I could push my luck, and ask for a way to
control
where the results would end up. Instead of being at the bottom of what
could
be a very long column, could the updated answer be at the top? Or
elsewhere?

Yes. If you've got cells in Excel time format, format the cells as .0
if
you want to display the time as seconds and tenths. Otherwise, of
course,
you could just work in seconds, rather than in time, and format the cells
as
number with one decimal place.
In row 2 put the formula
=IF(A2="","",AVERAGE(A$1:A2)), and copy down.
This will give you an average from the first row to the latest one as
it's
added, leaving each average cell blank until the corresponding row is
completed.
If you want only the latest average shown, you could change the row 2
formula to
=IF(A3="",IF(A2="","",AVERAGE(A$1:A2)),"")
--
David Biddulph

I would like to know the best method to average time. I would ideally
like
to
keep the time in seconds.tenths [sss.0]. Additionally I would like to
keep
a
rolling average of the compiled results. So that as new results are
added
they will be calculated with the old results, and produce a possible
new
average, to be saved until the next set of results are added. Is this
possible? And can this be somewhat automated?