Hello Dear Forum,

I've got a problem to get my situation solved. It took me now days to think about possible ways to get it solved but it just didnt work out for me.


Please see the attached xls file to see what I am facing. I am pretty sure that somebody could solve it and hopefully describe what and why he did.

I solved my problem manually by clicking and typing but it takes really hours and good eyes to get reasonable results. Somehow it must work also automatically.



Situation:

We have a race of total 77 laps with a laptime of 120seconds per lap, when the tires are new and fresh.

With every lap going on, the tire is dropping of in terms of performance. The TireDropOff-Formula for every TireOption like A,B and C are shown in the graph on the right.

That means that if you are racing 77 laps only with TireOptionA and not changing Tires again, you are going every ongoing lap slower due to performance dropoff described by the formulas in the graph.

So maybe it would be quicker to change Tires to be faster in the total racing time but with every stop (tire change and time reset because the tireset is new, so reset to laptime 120sec) you are losing 12 seconds because of changing in the pit.


Example:



Race: 77 Laps
Laptime: 120sec
Estimated Ideal Duration: 77*120=9240seconds

0 STOPS:
With only Tire Option A AND 0 STOPS it would take 120sec*77Laps+0,027*exp(0,2315*77laps)=1498189 seconds
With only Tire Option B AND 0 STOPS it would take 120sec*77Laps+0,0964*exp(0,1234*77laps)=10530 seconds
With only Tire Option C AND 0 STOPS it would take 120sec*77Laps+0,2256*exp(0,059*77laps)=9261 seconds
So Only Tire OptionC and 0 STOPS would be the quickest solution to get through the race.

Exercise:

Target is to see out of all possible Variations with the three TireOptions, which is the fastest solution to get through the 77 Lap Race.
Is it 0 Stops, 1 Stops, 2 Stops, 3 Stops, maybe 4 stops?
Is it OptionA mixed with Option B or even mixed with OptionC or start with OptionA and change only once to again OptionA?
But pay attention, that every stop costs 12 seconds, so there is a time loss when changing to new tires.

Excel should automatically calculate:


- The minimum total race time and minimum stops for all possible tire options
- Output should be like listed so graphs can be created to visualize the for example 5 strategy options




Somehow it must be possible to get it done in excel or do I need really programming skills for it?

Attached Files

question.xlsx (18.8 KB, 1 views)

Not sure yet how to solve this but I've got 3 main questions:
1. Your calculation:
With only Tire Option A AND 0 STOPS it would take 120sec*77Laps+0,027*exp(0,2315*77laps)=1498189 seconds
According to your chart y axis label: Time Loss Per Lap [sec/Lap]
the 0,027*exp(0,2315*77laps) part is for the added time for the 77th lap only, which is 1488949. The time added for the 76th lap would be 1181247 secs. So I don't think your calculation times for the whole 77 lap race are correct.

2. Of course, for Tyre A option the trendline is way out; your data shows 12 secs added fo the 30th lap, but the trendline is showing about 27, and it only gets worse with greater lap numbers. You get a better fit with a Power trendline or possibly the 2nd order polynomial.
Should we change that?

3. What version of Excel are you using? More specifically, which of the following can you use as part of a formula in a cell:
=SEQUENCE
=LAMBDA
=VSTACK

Finally, have you posted the same, or very similar, question elsewhere?