0

I want to give instruction to an Excel to give me a sum of certain amount by changing only Quantities. In the pictures, let say I want to get the sum of 250 (cell H16) by changing cells G5:G15. I can do that by using Solver, but I also need to include some restrictions or rules for Excel to use. First of all, my quantities have to be integer numbers. Secondly, I want an Excel to return numbers for all changing cells (G5:G15). Sometimes Excel does not fill all selected changing cells and returns numbers only for 3 cells.

Any help or idea would be appreciated. Many thanks in advance.enter image description here

Mr.Fatality
  • 1

2 Answers2

0

If your requirement is that variables should be integer and non-negative you may get the solution in which part of the variables is equal to 0. You may add another restriction that the variables should be >=1, but in case of small values of your goal this leeds to unreasonable results.
See the screenshots. Solver1
Solver2

MGonet
  • 4,015
0

To implement constraints in Excel solver, those need to be implemented as formulas between the inputs the solver can change and the result that is controlled.

To ensure every quantity has a minimum, you need to insert an intermediary column, eg filtered quantity in D2, with the constraint implemented as formula: ; where C2 is the raw quantity cell the Solver must change.

Then use this filtered quantity cell, instead of raw quantity, to compute your Price formula in E2.

A B C D E
1 Product Unit Price Raw quantity Filtered quantity
2 X 22.84 54 54 =TRUNC(MAX(1,C2))
Gus
  • 16