How can I create a rolling average between 2 values?
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 |
All times are GMT +1. The time now is 03:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com