find a combination of numbers that equal a given sum

mcgerks

New Member
Joined
Mar 24, 2002
Messages
12
Hi,

If I have A1 = 1, A2 = 3, A3 = 5, A4 = 7, and A5 = 9 in column A, Would someone know how to get the combination of numbers in column A that equal 13, or 24 for example?

Thanks for the help
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hi mcgerks.

How can you possibly achieve this ?
You could arrive at 13 in at least two ways . . .
9+3+1
7+5+1
 
Upvote 0
The solver can do this sort of stuff. Suggest you google for examples - I have seen something like you're after but unfortunately I can't locate it now.
 
Upvote 0
This is a question often asked by people trying to do bank reconciliations. There is a way of doing it without using VBA, which is not 100% reliable, but may help. Fisrt of all, make sure you have the Solver Add-in installed. What you then need to do is to line up all your source numbers in, say, column A. Then, in column B, put zeroes in every cell next to the values in column A. At the bottom of your column of zeroes (let's say cell B11), put the formula =SUMPRODUCT($A$1:$A$10,$B:$B$10) (I'm just assuming there are only 10 values, of course there could be more).
Now in the Solver, in the first dialogue box, enter $b$10 as your Set Target Cell and make sure you have put the value you need in the place for Equal to: Value of:
In the By Changing Cells box, enter $B$1:$B:$10
Now click on Add next to where it says Subject to the Constraints and enter that $b$1:$b$10 = binary, then OK
Now click on Options and tick Assume Linear Model, then OK
Then click Solve

If there is a correct answer, the relevant cells in $b$1:$b$10 will become 1 instead of 0, and you will have your answer. This usually works, but is subject to the following constraints. Firstly, it is better with short-ish lists of numbers, and secondly, if there is more than one solution, it will only give you the first one it finds.
 
Upvote 0
This is a great formula and thanks, Peter, for posting it. However, there are a few typos in the post and I thought I'd clarify for other users.

The first formula should read =SUMPRODUCT($A$1:$A$10,$B$1:$B$10). In the Solver, the Set Target Cell should be $B$11 and the 'By Changing Cells' box should contain $B$1:$B$10.
 
Upvote 0
The page I referred to includes the Solver approach including a downloadable template.
This is a great formula and thanks, Peter, for posting it. However, there are a few typos in the post and I thought I'd clarify for other users.

The first formula should read =SUMPRODUCT($A$1:$A$10,$B$1:$B$10). In the Solver, the Set Target Cell should be $B$11 and the 'By Changing Cells' box should contain $B$1:$B$10.
 
Upvote 0
hey guys

this is what i need to do

but with a list of 1000+ records
solver = fail

i havent tried cutting it down into sections
because some numbers could be across sections :/

anyone know of a way to punch out 1K+ records?

find the numbers that sum to zero?
 
Upvote 0
I checked the MT website - some nice advice - though im too much of a noob to build macros - so more research for me
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top