×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Microsoft: Office FAQ

## Best of Excel

 Which of these numbers add up to my target number?? by KenWright faq68-4742 Posted: 20 Jan 04 (Edited 9 May 04) If you are looking for a solution (Not necessarily the only one) to a subset of a group of numbers that will add up to a target number, then this can be done quite simply with Solver.Assuming range of numbers in A1:A30, add a set of 0s in B1:B30 and in say B31 put=SUMPRODUCT(A1:A30*B1:B30)Now do Tools / Solver / Set Target Cell 'B31' to 'value of' and put in your targetnumber.  Then, using the range selector under the 'By Changing cells' section, select cells B1:B30 as the ones to change and hit enter which will take you back to the first dialog box.  Now hit the 'Add' button, and add the constraint that B1:B30 must be 'bin' (Means binary as in 1 or 0, and it's one of the dropdowns, so just hit the arrow and select 'bin') and just hit Solve.  You MUST ensure that in this example, when you add the 'bin' constraint range, you do not inadvertantly include the formula cell B31, else you will get an error message such as 'Binary Contsraint cell reference must include only adjustable cells'Won't do any more than single solution, but for a Finance Dept that will oftensuffice in this context.[color green]Thanks to Henio as well for the following link, which will give you a helpful tutorial at http://www.solver.com/stepbystep.htmand which walks you through an interesting scenario and explains what you can do with the tool.[/color]If you are going to look for more than one target number in the data, then withthat formula in say B31, in B32 type the target number, and in B33 put =B32-B31.Now have Solver solve B33 = to 0 with the same constraints.  Saves having tochange any values in Solver that way, just type what you want in B32.Looks quite neat too if you format B1:B30 to a white Font and then useconditional formatting to highlight values in Col A where Val in Col B = 1 Back to Microsoft: Office FAQ Index Back to Microsoft: Office Forum

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!