Hi there,
I have a sheet with a growing number of columns, each date has a start, end and duration. I would like to have one set of columns which will dynamically calculate the average of all appropriate columns. The layout is below:
I am currently manually adding the new date's information to a formula, but this is too much work and the number of characters seems to be starting to exceed the maximum. Below is an example of the start-time average formula (note the above data is in "DD/MM/YYYY HH:MM:SS" format, but formatted to only show time):
I was thinking of creating a macro which updates the average values, when the sheet is activated, for each row's start, end and duration. Any ideas would be appreciated.
Thanks,
possom
I have a sheet with a growing number of columns, each date has a start, end and duration. I would like to have one set of columns which will dynamically calculate the average of all appropriate columns. The layout is below:
Code:
----------------------------------------------------------------------------------------
| Average | 20/12/2004 | 21/12/2004 |...
----------------------------------------------------------------------------------------
Name | Start | End | Duration | Start | End | Duration | Start | End | Duration |...
----------------------------------------------------------------------------------------
proc1 | 02:40 | 03:00 | 00:20 | 02:10 | 02:45 | 00:30 | 03:10 | 03:15 | 00:05 |...
proc2 | 03:40 | 04:00 | 00:20 | 03:10 | 03:45 | 00:35 | 04:10 | 04:20 | 00:10 |...
proc3 | 04:00 | 04:20 | 00:10 | 04:00 | 04:20 | 00:10 | 04:00 | 04:20 | 00:10 |...
...
I am currently manually adding the new date's information to a formula, but this is too much work and the number of characters seems to be starting to exceed the maximum. Below is an example of the start-time average formula (note the above data is in "DD/MM/YYYY HH:MM:SS" format, but formatted to only show time):
Code:
=AVERAGE(TIME(HOUR(J4),MINUTE(J4),SECOND(J4)),TIME(HOUR(M4),MINUTE(M4),SECOND(M4))...
I was thinking of creating a macro which updates the average values, when the sheet is activated, for each row's start, end and duration. Any ideas would be appreciated.
Thanks,
possom