Setting priorities in Excel Solver

Question

Excel Solver (https://i.sstatic.net/ZLQyOB9m.png)

Hello everyone, right now I am working on a Solver in Excel. Now, I am trying to find a solution for below problem:

SOLVER FUNCTION: To select a combination of cable sizes that will give an average voltage drop of less than 3%, while optimizing cost. Price of cable increases with size i.e. 4mmsq is cheapest, 6mmsq is middle, 10mmsq is most expensive.

PROBLEM: Referring to photo attached, I need every row to only select either 4mmsq, or 6mmsq, or 10mmsq (selected size is reflected by the number 1). Best case scenario is to have all 4mmsq. If this is not possible (voltage drop exceeds 3%), some of the rows need to use 6mmsq. If all rows are already using 6mmsq and voltage drop still exceeds 3%, then some of the rows will need to use 10mmsq.

My question is, how do I tell solver to prioritize 4mmsq first, 6mmsq second, and lastly 10mmsq? Right now the solver is randomly jumping straight to 10mmsq, when sometimes using 6mmsq would solve the problem.

Hopefully my question is understandable, thanks all.

I am working on a Solver problem in Excel to select a combination of cable sizes that ensures an average voltage drop of less than 3% while optimizing costs. The cable sizes range from 4mmsq (cheapest) to 10mmsq (most expensive), with 6mmsq being in the middle. Each row in the spreadsheet must select one cable size, indicated by a '1' for that size. Ideally, all rows would use 4mmsq, but if the voltage drop exceeds 3%, some rows should switch to 6mmsq, and if needed, to 10mmsq.

I've tried setting up the Solver to achieve this, but it tends to jump straight to the 10mmsq option, even when using 6mmsq would suffice. I'm looking for a way to instruct the Solver to prioritize 4mmsq first, then 6mmsq, and lastly 10mmsq to ensure cost-effectiveness

For the person who did the vtc, there is enough info here to understand the issue. — Solar Mike
– Solar Mike, Commented Jul 1, 2024 at 9:40

Solar Mike · Accepted Answer · 2024-07-01 09:39:10Z

0

So you need to have a "rating" criteria, such that 4mm is looked at first, if that is not possible then 6mm is considered etc

This will stop the Solver using the 10mm first, but cost should also be helping with this unless you have not specified that cost criteria correctly. One possibility is that the relative difference between the costs may need to be increased as the Solver algorithm may ignore smaller differences.

One thing to try is to have a factor of 10 as the difference in cost just for the Solver, while recalculating the real prices outside the Solver set criteria.

answered Jul 1, 2024 at 9:39

Solar Mike

8,4255 gold badges23 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Setting priorities in Excel Solver

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related