formula to recalculate lap times when actual times vary from goal times

Hi All,
I'm looking for help to formulate new lap goal times when the actual times vary from the planned goal times.
Column A is actual Lap
Column B is target lap times to achieve the goal amount of laps (cell B3 is goal laps and C3 is time allowed in minutes. 600 being 10 hrs.)
Column D is what i want to calculate. Id like it to update based on the actual lap times entered in Column C. EG: 30 laps in 10 hrs currently sets the target laps at 20 minutes each. If lap one was completed in 15 minutes and then id like the remaining laps to be adjusted taking into consideration the 5 minutes and that were saved on lap one. I would also be happy if this could be formulated into column B if possible.
something else to consider is if I reduce the Goal laps then id like the formula to be aware of this so if in the example above where 5 minutes was saved on lap one, that 5 minutes would be shared between the remaining goal laps and not a set 30 lap goal.

another example:
I want to complete 10 laps in 1 hour so this would mean I need to complete each lap in 6 minutes as its 60 minutes divided by 10.
If I was to complete the first 2 laps in 5 minutes each that would then leave me with 50 minutes to complete the remaining 8 laps as I've banked an extra 2 minutes from completing the first two laps 1 minute faster each. This works out as 6 minutes and 15 seconds per lap. (basically if each lap is faster than what's required then that extra time saved needs to be dispersed evenly over the remaining laps). I'll see if I can add some manual data to the sheet to show better.

Please try in C5 and copy down:

Formula:

Please Login or Register  to view this content.