Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-30-2023, 05:11 PM
vx110 vx110 is offline Excel Automatical Solving Problem Windows 10 Excel Automatical Solving Problem Office 2016
Novice
Excel Automatical Solving Problem
 
Join Date: Jan 2023
Posts: 1
vx110 is on a distinguished road
Default 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?
Attached Files
File Type: xlsx question.xlsx (18.8 KB, 2 views)
Reply With Quote
  #2  
Old 01-31-2023, 09:12 AM
p45cal's Avatar
p45cal p45cal is offline Excel Automatical Solving Problem Windows 10 Excel Automatical Solving Problem Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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; 02-01-2023 at 04:50 AM.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help solving a corrupt image issue valentinator Word VBA 1 06-12-2022 03:17 PM
Excel Automatical Solving Problem Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem JennEx Mail Merge 6 05-26-2019 06:08 AM
Excel Automatical Solving Problem solving a problem?! excel-word alin_d Excel Programming 2 11-29-2015 07:59 AM
Excel Automatical Solving Problem Automatical email when sheet is updated? TB12 Excel 3 06-24-2014 09:00 AM
Excel Automatical Solving Problem Automatical backgroundcolor change based on cell value ChiCotje Word Tables 2 02-20-2013 01:13 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:07 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft