Share via

Using solver to find a combination of numbers that equal a specific number.

Anonymous
2023-06-05T04:39:38+00:00

Hello, I'm do understand how to use Solver to get a specific number. I am wondering how can I make it so if there is no combination I can make it so I can get as close as possible without going over?

I add a simple file to my drop box. 

I hope it is even possible. 

​https://www.dropbox.com/scl/fi/fm21xzd09v6pay91c1abi/closest-sum-04-June-23.xlsx?dl=0&rlkey=2zolhj0xzin7suogas7oug1gg

Thank you,

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-06-05T09:18:05+00:00

    Hi Embry!

    I am Shakiru, an independent advisor and a user like you, and I am glad to be helping you out today.

    Since you have set up your spreadsheet, set up the mathematical model and define the objective by using the SUM function

    The next you are to do is this: * You need to define the constraints for Solver. In this case, you want the sum in a cell to be equal to the specific number in another cell. You can do this by adding a constraint in Solver.

    * Use Solver to find the combination In Excel, go to the "Data" tab and click on "Solver" (Note: Solver is an add-in, and you may need to install it if it's not available). Set the objective to be equal to the specific number in cell say A1 by changing the inclusion indicators in column C. You also need to define that the inclusion indicators should only be binary (0 or 1). Click "Solve" to let Solver find the combination that meets the objective.

    Remember to save your file after using Solver to find the combination.

    Kindly let me know, if you require additional assistance, I will be glad to help further.

    Best Regards, Shakiru

    1 person found this answer helpful.
    0 comments
  2. HansV 462.4K Reputation points MVP Volunteer Moderator
    2023-06-05T10:06:29+00:00

    With the default settings, Solver will try to find an extremely close match and run out of time. Even if you let it continue for a long time it probably won't find a solution.

    In the Solver dialog, click Options and set the constraint precision to a larger number, e.g. 0.1

    It should then at least find a solution within a reasonable time, even if it is not really the optimal one.

    Image

    0 comments
  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  4. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  5. Anonymous
    2023-06-06T15:38:42+00:00

    Excel 365

    In case you want to find all solutions between 699 and 700,

    there are three.

    Oodles more if you relax the resolution.

    Featuring the method of the legendary Harlan Grove.

    https://www.mediafire.com/file_premium/22rv0547t65ei7n/06_06_23.xlsm/file

    https://www.mediafire.com/file_premium/nes350103xu3m2a/06_06_23.pdf/file

    0 comments