Hi mcgerks.
How can you possibly achieve this ?
You could arrive at 13 in at least two ways . . .
9+3+1
7+5+1
This is a discussion on find a combination of numbers that equal a given sum within the Excel Questions forums, part of the Question Forums category; Hi, If I have A1 = 1, A2 = 3, A3 = 5, A4 = 7, and A5 = 9 ...
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
Hi mcgerks.
How can you possibly achieve this ?
You could arrive at 13 in at least two ways . . .
9+3+1
7+5+1
The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !
Sub Macro()
ActiveCell = "IY" & Right(Application.Name, 5)
With ActiveCell.Characters(Start:=2, Length:=1).Font
.Name = "Webdings"
.Color = 255
End With
End Sub
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.
Thanks Fazza the solver add-in did the trick!
-Keith
For future reference:
Find a set of amounts that match a target value
http://www.tushar-mehta.com/excel/te...ues/index.html
Tushar Mehta (Microsoft MVP Excel 2000-2015)
Excel & PowerPoint tutorials and add-ins; custom productivity solutions for MS Office
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.
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.
Tushar Mehta (Microsoft MVP Excel 2000-2015)
Excel & PowerPoint tutorials and add-ins; custom productivity solutions for MS Office
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?
I checked the MT website - some nice advice - though im too much of a noob to build macros - so more research for me
Like this thread? Share it with others