#1




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 TireDropOffFormula 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? 
#2




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? Last edited by p45cal; 02012023 at 04:50 AM. 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Need help solving a corrupt image issue  valentinator  Word VBA  1  06122022 03:17 PM 
Unexpected Big Problem With Mail Merge via Excel VBA  DataLink Problem  JennEx  Mail Merge  6  05262019 06:08 AM 
solving a problem?! excelword  alin_d  Excel Programming  2  11292015 07:59 AM 
Automatical email when sheet is updated?  TB12  Excel  3  06242014 09:00 AM 
Automatical backgroundcolor change based on cell value  ChiCotje  Word Tables  2  02202013 01:13 AM 