Add Complex Logic to Solver Constraints

Please see the attached file and follow along. I'm trying to create a solver/optimization for 2 variables. Cost and Capability Score.

Please see the constraints in the solver to understand the optimization. Basically, the allocation of Flight hours to aircraft must be done at the lowest cost possible while maximizing the capability score AND not exceeding the budget constraint or the required amount of flight hours for each capability.

While this solver works, I have noticed a few issues inherent with the optimization:

 1. It placed 500 Hover hours on the Fixed Wing Prop even though the fixed wing prop has a capability score of 0 when it comes to hovering....
The solver should never put any amount of hours on an aircraft that is "unacceptable" when it comes to performing the capability(ala fixed wing aircraft can't hover... duh

2. It placed 500 High Alt. hours on the Fixed Wing Prop because there wasn't enough $$$$ left to fly those on the Jet.... Okay this makes sense, but what if I consider High Alt. Hours more important than say the Hover Hours. I think this is where a "Importance Score" has to play a role. For example, what if it is more desirable fore me to have a SOLVEDNUMBER of high altitude hours performed by a Jet than it is to have a SOLVEDNUMBER of Hover hours performed by a Helicopter.

Conceptually, the idea of #2 and an Importance Score seems logical; however, its incorporation into the solver constraints is what I can't figure out...

3.  Lastly, I told excel that anything in the green shaded area should be an integer, solver doesn't seem to want to listen to that.

PS If there is a better way to formulate "lowest cost" please let me know, I didn't know how to articulate this idea without including a manual budget constraint field
OptimizationExampleEEversion2.xlsm
LVL 1
-PolakAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AlanConsultantCommented:
Hi Polak,

There is a reference in the code to 'SolverSolve' - is that more custom code?

If so, please can you supply that too.

Thanks,

Alan.
0
-PolakAuthor Commented:
No, I was just following this walkthrough on how to make a clickable button to run a solver.
Tutorial: Creating a Command Button to Run Solver

At the end it says to create the reference.
0
AlanConsultantCommented:
Hi,

Apologies for taking so long to get back to you - I was away.

Ignore my post above - I was on the wrong track.  Let's resolve the issues one at a time if we can.

I have attached a Version 2.1 of your file that constrains the fixed wing to having zero hover hours.

Does that resolve the first issue for you?

Alan.
OptimizationExampleEEversion2.1.xlsm
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

-PolakAuthor Commented:
hhhmmmm, no I don't think so, with your example no matter how the capabilities matrix changes your solver always solves for 750 Hover Hours on a Helicopter, 500 High Alt. Hours on a Fixed Wing Prop, and 1000 LR Surv. Hours on a Fixed Wing Prop.

For example, if I change the entire capabilities matrix to 0's for all aircraft and capabilities your solver still distributes the hours as I described above....
0
AlanConsultantCommented:
Okay, try this (Version 2.2)

I found I had to click the button a couple of times to get it to give me proper (integer) answers sometimes, but it does seem to work.

What do you think?

Alan.
OptimizationExampleEEversion2.2.xlsm
0
-PolakAuthor Commented:
Hi Alan,

I see what you did there.... Couple of questions/issues that arose during testing:

1.

Changing nothing, as soon as I Press the solver button I get a different distribution of Flight Hours then what you have in version 2.2..... it moves the 375 Jet Hover hours to 750 Helicopter Hover Hours. I want to make sure its not giving us different solutions for the same budget and capability variables....

2.

Sometimes the values in the Green area need to be deleted before the solver will re-solve. For example, taking version 2.2 as sent, change all of the capabilities matrix values to 0's. Then press the solver button. It will not redistribute the hours. If you then delete the values in the green area and press the solver button again it will place 1 hour on each aircraft and capability type.

3.

Delete all values in the green area, increase the budget constraint to 3,000,000,000(or just something really high); then, change all values in the capabilities matrix to 4's.  Press Solver button again, all capabilities being equal you'll see that the solver doesn't distribute costs to the lowest cost option to fly per hour.

4.

Lastly, I see the constraints that you placed in to attempt to solve the if capabilities are equal to 0 problem. However, the real workbook is going to need well over 200 of those constraints to accomplish the same. Wondering if this can be written as a range to simplify.... also I have read that solver has a limit to the number of constraints that can be input... don't want to run into that

Let me know your thoughts....
0
-PolakAuthor Commented:
Hi Alan, just as a heads up I'll be out of pocket from tomorrow morning until Tuesday; so don't think I'm ignoring you.

I feel as if item 3 from my above list is going to be the most difficult to tackle, clearly the "PS" of my original question is the problem here. Somehow a Min function has to be written into the constraints and I'm not sure how/if this is possible.
0
-PolakAuthor Commented:
For anyone looking at this, I was thinking that maybe increasing capability score up to the same scale as cost (millions-to-hundreds of millions) then subtracting cost might be the way to go to express "lowest cost" let me know what you think...
0
AlanConsultantCommented:
Hi All,

Apologies for going quiet - I have been away over Easter, but back today.

If you want to close / re-do the question, no issue from my side.  In the meantime, I'll pick it up again later today once I've cleared the mountain of emails I have sitting in front of me!

Alan.
0
AlanConsultantCommented:
Hi Polak,

Took me longer to get through the emails than expected, but I have had another look now.

I think the 'capability score' calculation might be the issue.

If I lift the budget constraint up very high, and set all the capabilities to 4, and optimise, I can get a capability score of 10,000 for multiple answers.

Example1:
Heli = 0 / 0 / 0
Jet = 1000 / 0 / 0
FWP = 0 / 500 / 1000
Capability Score = 10,000

Example2:
Heli = 800 / 0 / 0
Jet = 100 / 0 / 0
FWP = 100 / 500 / 1000
Capability Score = 10,000

Since you are maximising the capability score, both are equally valid, albeit not equally costly.

Therefore, it seems to me that the capability score algorithm does not adequately discriminate between potential valid solutions in that it takes no account of cost?


Does that seem right to you?

Alan.
0
-PolakAuthor Commented:
yes, exactly, this speaks to #3 in my points above and to the larger point of there not being a good way to "administer ties".

Basically, we need to factor cost per flight hour into the calculation of capability score. So I was thinking, would it make sense to raise capability score to the same scale as the budget constraint and then subtract cost from capability score? Would that not articulate "lowest cost" as opposed to just staying under a budget constraint?
0
AlanConsultantCommented:
Hi Polak,

I can only really guess, since I have no particular experience in aircraft operations / management, but what you are saying does seem to make some kind of sense.

One general method that I apply to such problems is to ask myself:  Do the units lend themselves to that approach?

If I look at the calculation of 'Capability Score' the sumproducts are hours x capability, from which you would be deducting cost (dollars or whatever currency you are working in).  Therefore, there is no obvious match, but it depends on what the unit of 'Capability' is.

If we make an assumption that capability is some measure of efficiency, and that the units of that efficiency are, to pick not entirely at random, cost per hour (albeit expressed in some oddly scaled units), then the Capability Score would have units of:

Hours x (Cost / hour) = Cost (dollars)

I have attached Version 3, changing the Capability Matrix units to be percentages that get applied to the Flight Hour Costs, and renamed (to avoid confusion with previous versions - you can use whatever narrative / labels you like of course).

Please note that my allocation of 100% / 75% / 50% / 25% / 0% is entirely arbitrary, but then so were the use of 4 / 3 / 2 / 1 / 0.

I have also changed the VBA to run the solver 20 times (also arbitrary!) as I have some experience in the past where I had to run solver more than one to get a final, stable answer.  There is also an 'iterations' setting that you can use somewhere, but I have always liked the explicit nature of having it in my VBA where I can add comments to myself (or others) to my heart's content :-)

This one does seem to give an answer that makes some sense.

What do you think?

Alan.
OptimizationExampleEEversion3.1---Change
0
-PolakAuthor Commented:
Hi Alan,
We're getting into some pretty exciting conceptual stuff, well at least I find it exciting....

Okay so to your point "depends on what the unit of 'Capability' is". Capability is a measure of fit/appropriateness so conveniently the unit doesn't really matter.... you are very much correct, in that once you subtract $'s it becomes a measure of efficiency. Ergo, the optimization (capability vs. cost) I was searching for at the beginning of this question.

However, in-terms of user-input, Percentages do not lend themselves as well as a traditional 1-5 or 1-10 or in this case 0-4 scales. Because I'd eventually like to make this a stochastic model that simulates results for an input range of 1-to-4 in the Capability FIT matrix. I'd like to keep the fit matrix in a unit that lends itself to user input. (ala "I'd like to test Helicopter>Hover at 1, 2, 3, and 4" is easier to conceptualize than "I'd like to test Helicopter>Hover at 25%,50%,75%,100%")

Naturally, without a percentage, the issue then becomes scaling, because the calculation of Capability Score (without a cost component) is so low and the subtraction of a cost component is so high (in the millions). I know solver has an auto-scaling option, but I'm not sure how that works.... Therefore the new formula multiplies capability score by 1000 before subtracting costs. FYSA, in the real world the range of costs are $365-$4500, not sure if fixed scaling by 1000 will cause an issue...

(Also, Apologies for the clunky formula I tried to make it fancy, but just couldn't).

What do you think about the end result? I'm still having issue #2 from above and I have no idea why....
OptimizationExampleEEversion3.5-Polak.xl
0
AlanConsultantCommented:
Hi,

No problem with using 1 through 4 - as I said above, they are arbitary, but so are the percentages, so whatever you prefer I guess.  It would be 'nice' to prove that these are somehow efficiencies, and to actually calculate them in some way, but that is perhaps an exercise for later!

Solving issue two by way of a workaround - I have added a line to clear the green section of any pre-existing solution before running the solver.

See attached.

How does that look?

Alan.
OptimizationExampleEEversion4.1.xlsm
0
-PolakAuthor Commented:
Beautiful! One issue I noticed during testing....

To duplicate my issue:
Set all of Helicopters Capability Fits to "0"
Re-run the solver

You'll notice that it splits the hours between Jet and FWP despite FWP being .01 cents cheaper to operate. Ready for the really weird thing?

Set Jet's cost to 1003.01 and all hours now correctly go to the FWP.... It seems that at < $3.01 of difference and when only dealing with TWO aircraft types it wants to split the hours evenly again... Obviously, this isn't real-world, and the problem doesn't exist with 3 aircraft types, but I'd be curious to understand why and if the methodology is showhow flawed?

PS if you see an easy way to clean up the formula for capability score lemme know.
0
AlanConsultantCommented:
Hi Polak,

In terms of the 'odd' splitting of hours, I have changed this to to operate to a higher level of precision which solves it I think.

Does that work for you?

Thanks,

Alan.
OptimizationExampleEEversion4.2.xlsm
0
-PolakAuthor Commented:
Hummm Looks like the problem returns in version4.2 if I put Helo back to 4, 4, 4...... and solve again

If I make the solver a Simple LP instead of a GRG Nonlinear it seems to fix the issue; however, without knowing more about those two Simulation Modes I really have no idea which is more appropriate?
0
AlanConsultantCommented:
Hi Polak,

I have generally taken the view previously, that if one works better than another, then that's the one I go with - not very scientific I accept!

This is perhaps more helpful:

https://support.office.com/en-ca/article/Define-and-solve-a-problem-by-using-Solver-9ed03c9f-7caf-4d99-bb6d-078f96d1652c

I see no reason to assume that your requirement is non-linear, so LP Simplex seems to be the best option.

If LP Simplex seems to work for a variety of input values, then I'd stick with that.

HTH,

Alan.
0
AlanConsultantCommented:
Hi Polak,

I have updated the formula for the 'Capability Score'  in the attached Version 4.3.

I believe it is logically equivalent to what you had, but you might want to try some inputs to make sure.

Alan.
OptimizationExampleEEversion4.3.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
-PolakAuthor Commented:
Awesome, I think we've done enough for this one question, as a note I changed the scaling to 10000 (vs 1000) so that it is clear that capability fit is always the more "important" variable for the solver to consider. This eliminated the situation where if you increased the budget constraint to a very large number (50 Trillion) lets say, that it would reliably allocate the hours on Aircraft & Capabilities ranked "4".

Thanks for your help on this, if I post any follow-ups to enhance I'll be sure to message you.
0
AlanConsultantCommented:
My pleasure.

Thanks,

Alan.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.