Excel Solver Product Scheduling

I changed the formula of "L1" to

Please Login or Register  to view this content.

And it seems to work totally fine. Not sure I'm understanding what you're saying about the Lookup equations.

Sorry that Sum was already supposed to be there copied over this sheet and it wouldn't copy over the formula so had to recreate them all, guess I missed that one. What I am referring to is after you run the Solver the Vlookup will only reference whatever cell it Orders as 1 for every Vlookup formula throughout the sheet. You can see this by evaluating a few of the formulas with "Evaluate Formula" in the Formula tab. After I sort the order the Vlookup formulas reset and reference the correct cells, but once sorted it doesn't give the optimum ordering. I can sort manually by Time then Temp and get a Delay around

Delay around 1600

All Temp and Time Changes and Delays have 0 values after solver runs except one row. This is not possible. If you evaluate the formula and follow what data it pulls in it doesn't pull in the correct reference.

Added corrected file. It was missing an iferror function.

Ok I have absolutely no idea why those VLOOKUPs were not working, but I think I fixed the problem by changing your formula to something that uses INDEX and MATCH. Let me know if this is what your end goal is

Oh never mind I didn't realize the other column was incorrect as well I'll have to have a look at that

Updated version: Delay = 1595

Thank you so much!! I couldn't figure out how to do it other than vlookup.

The solver runs now and gives an answer, but it isn't as optimal as I can get it sorting manually (it is close though). If you have any experience with the evolutionary settings, what setting should I change to get a better result before it stops?

Yea I noticed the other column wasn't changed to INDEX and MATCH so I changed it and ran it. I got it down to 1595 through the solver as well but through manually sorting a few rows I was able to get it down to 1540. So trying to figure out how I can get the solver to arrive to the same conclusion. Trying to change the mutation rate to see if that helps.

I'm not really sure sorry - I've only ever used the Linear settings for the solver. From what I understand though, increasing the mutation rate to something closer to 1 should get you to a better solution, but I'd do it slowly as it may drastically increase the time it takes.
I tried running it with non-linear and it's already gone through 85,000 iterations (it's been running for ~5 minutes). I have no idea if it will actually reach a solution just figured it was worth a shot
Edit: I paused it and the current best solution is 1485, so it definitely does give a good answer, it will just take an extremely long time to find the optimal one

Thanks again, you have been a huge help. I changed the mutation rate and put a max run time on it and got it down to 1470. I will close this post as solved.

Glad I could help!