Excel Automatical Solving Problem
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?
|