# Solver to find the most possible options with out going over.



## AWM21 (Dec 23, 2022)

Hello, thank you for looking at this with me.

I have 13 options and I need to select the highest count of these options, where their items costs totals cannot exceed a fixed number. (168) in this example.
Out of the options below, the goal is to include as many options without exceeding the fixed total. The totals can fall short of 168, just not exceed.

I used solver, but solver gets me the least amount of options to include, I need the most. 

here is my data. Thanks again!

Book1.xlsxBCDEFGHIJ89Opt 0180180Total of all options76010Opt 0263163Opt items totals cannot exceed16811Opt 0318118Difference between above59212Opt 048018013Opt 053613614Opt 068018015Opt 076016016Opt 088018017Opt 098018018Opt 1080180Total post Solver solution76019Opt 115415420Opt 122112121Opt 132812822Sheet1Cell FormulasRangeFormulaI9I9=SUM(F9:F21)I11I11=I9-I10I18I18=SUM(F9:F21)F9:F21F9=E9*D9Named RangesNameRefers ToCellssolver_adj=Sheet1!$E$9:$E$21F9solver_lhs1=Sheet1!$E$9:$E$21F9solver_lhs2=Sheet1!$E$9:$E$21F9solver_lhs3=Sheet1!$E$9:$E$21F9solver_rhs4=Sheet1!$I$10I11






Book1.xlsxBCDEFGHIJ89Opt 018000Total of all options16810Opt 026300Opt items totals cannot exceed16811Opt 031800Difference between above012Opt 04800013Opt 05360014Opt 06800015Opt 076016016Opt 08800017Opt 09800018Opt 1080180Total post Solver solution16819Opt 11540020Opt 12210021Opt 132812822Sheet1Cell FormulasRangeFormulaI9I9=SUM(F9:F21)I11I11=I9-I10I18I18=SUM(F9:F21)F9:F21F9=E9*D9Named RangesNameRefers ToCellssolver_adj=Sheet1!$E$9:$E$21F9solver_lhs1=Sheet1!$E$9:$E$21F9solver_lhs2=Sheet1!$E$9:$E$21F9solver_lhs3=Sheet1!$E$9:$E$21F9solver_rhs4=Sheet1!$I$10I11


----------



## Peter_SSs (Dec 23, 2022)

Is this what you want?

22 12 24.xlsmCDEFGHI9Opt 018000Total of all options16310Opt 026300Opt items totals cannot exceed16811Opt 0318118Difference between above-512Opt 048000Options used513Opt 053613614Opt 06800015Opt 076016016Opt 08800017Opt 09800018Opt 10800019Opt 11540020Opt 122112121Opt 1328128SolverCell FormulasRangeFormulaI9I9=SUM(F9:F21)I11I11=I9-I10I12I12=SUM(E9:E21)F9:F21F9=E9*D9


----------



## AWM21 (Dec 25, 2022)

Peter_SSs said:


> Is this what you want?
> 
> 22 12 24.xlsmCDEFGHI9Opt 018000Total of all options16310Opt 026300Opt items totals cannot exceed16811Opt 0318118Difference between above-512Opt 048000Options used513Opt 053613614Opt 06800015Opt 076016016Opt 08800017Opt 09800018Opt 10800019Opt 11540020Opt 122112121Opt 1328128SolverCell FormulasRangeFormulaI9I9=SUM(F9:F21)I11I11=I9-I10I12I12=SUM(E9:E21)F9:F21F9=E9*D9
> 
> View attachment 81516



Thanks Peter! that works! 
Looks like I was referencing the wrong cell for my objective, and I needed to select "Max" instead of defining a value. 
Thanks again!!


----------



## Peter_SSs (Dec 25, 2022)

You're welcome. Thanks for the follow-up.


----------

