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