Formula Showing Average Pace in Minutes for Positive Splits Falling Into a Set Time

[hatkevtonin]

Hello, I could use some help. Here is my problem:

There are multiple laps of equal distance.
Each lap is slower than the previous.
During each lap there is a estimated time at rest.
I would like to be able to calculate the average pace needed to complete the next laps under these conditions and factoring in the estimated time at rest but still finishing before time runs out.

The formula I have assumes the same amount of time per lap:

overall average pace = average pace for 4 hours + time at rest
0:15:07 = (((240/16.6)/60)/24)+((SUM(B15:B19)/16.6))

I think I need to replace the "240" with a formula that creates an ascending value that still equal 1440 when all laps+time at rest are summed. I'm struggling to figure out how to write that formula.

This is as much a math problem for me as it is an excel problem, and I'm not great at either.

If you can decipher this and help, I appreciate it. Thanks!

[JeteMc]

Hello hatkevtonin and Welcome to Excel Forum.
Perhaps this will be helpful.
I am thinking that instead of replacing 240 with a formula, you can adjust the rest time.
In order for the equation in post #1 to work SUM(B15:B19) must equal 10:48. Assuming that 15:07 is the lap time for the 5th lap and that the rest times for each lap are the same (~02:10) you would exceed the 4 hour limit during lap 16. Using the Goal Seek feature to find the rest time that could be allowed each lap you get a value of 01:04. (modeled in columns B:D)
On the other hand if the 2:10 per lap is a mandated rest time then Goal Seek finds that the value of 240 will need to be adjusted to 230.5833 (columns K:L)
I am just guessing with all of this. It would be much easier to help if you could upload a sample spreadsheet that shows what you are trying to accomplish. To upload a sample spreadsheet (not a picture or pasted copy) click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

[hatkevtonin]

Thanks for taking a look at this. I'm exploring that Goal Seek feature but so far it's not doing what I think I'm telling it to do.

I attached a simplified version of what I'm working on, hopefully it clarifies my objective.

[JeteMc]

I don't think that this is correct, however I hope that it provides a starting point.
I applied a modified version of the formula from post #1 to column E: = G$1/B3/60/24+SUM(D$3:D3)/B3
Column G totals the amounts in column E.
Initially the value 1440 was in cell G1.
I set up the Goal seek to set cell G8 to 1440 by changing cell G1. The values in column E do follow the rule of being ranked from fastest to slowest, provided that those values are the amount of time (it may be helpful to know whether this is measured in seconds, minutes etc) that it takes to complete the Lap.
On the other hand the values in column C, populated using =E3-D3, aren't arranged in fastest to slowest order. For this reason I believe that I am not understanding how the sample should be completed. Is it possible to upload an even simpler sample, and manually fill in the expected values so that we may attempt to match those values using formulas and/or code. We could then attempt to transfer those formulas/that code to the sample attached to post #3.
Let us know if you have any questions.
Edit: The formula populating column C should read: =E3-SUM(D$3:D3) (file adjusted)

[JeteMc]

Perhaps this is closer to what you are looking for. Solver is used with constraints that C3 = 100 (arbitrary unless there is a minimum amount of time) then each subsequent cell in column C set to greater than or equal the preceding cell multiplied by 1.001 (again an arbitrary value).
Let us know if you have any questions.