Hi,

I have an excel spreadsheet and need to create average times in between a
start time and finish time within a single column. For instance, if A1
contains 10:00 & A10 contains 12:00 cells A2:A9 should contain a rolling
average between them so A2=10:12, A3=10:24 and so on to create a staggered
set of times. The number of cells in between the start and end times has to
be variable and it ideally needs to be able to deal with situations where the
start & finish times go accross midnight. The times need to be formatted
American style with hours & minutes (10am as 10:00).

Many thanks

Try the following:

Use this formula:
=$A$1+(ROW(A2)-ROW($A$1))*($A$10-$A$1)/ROWS($A$1:$A$10)

For the crossing midnight problem, the easiest solution I can think of is:
Format the cells as just a time (perhaps with AM and PM). However, enter
the times as full dates, such as 1/1/05 2:00 PM and 1/2/05 1:00 AM. These
should show up fine, but will use the date to account for crossing midnight.

Art

"tomcat" wrote:

Hi,

I have an excel spreadsheet and need to create average times in between a
start time and finish time within a single column. For instance, if A1
contains 10:00 & A10 contains 12:00 cells A2:A9 should contain a rolling
average between them so A2=10:12, A3=10:24 and so on to create a staggered
set of times. The number of cells in between the start and end times has to
be variable and it ideally needs to be able to deal with situations where the
start & finish times go accross midnight. The times need to be formatted
American style with hours & minutes (10am as 10:00).

Many thanks

Many thanks Art - elegant and accurate solution!

"Art" wrote:

Try the following:

Use this formula:
=$A$1+(ROW(A2)-ROW($A$1))*($A$10-$A$1)/ROWS($A$1:$A$10)

For the crossing midnight problem, the easiest solution I can think of is:
Format the cells as just a time (perhaps with AM and PM). However, enter
the times as full dates, such as 1/1/05 2:00 PM and 1/2/05 1:00 AM. These
should show up fine, but will use the date to account for crossing midnight.

Art

"tomcat" wrote:

Hi,

I have an excel spreadsheet and need to create average times in between a
start time and finish time within a single column. For instance, if A1
contains 10:00 & A10 contains 12:00 cells A2:A9 should contain a rolling
average between them so A2=10:12, A3=10:24 and so on to create a staggered
set of times. The number of cells in between the start and end times has to
be variable and it ideally needs to be able to deal with situations where the
start & finish times go accross midnight. The times need to be formatted
American style with hours & minutes (10am as 10:00).

Many thanks