Page 1 of 2 12 LastLast
Results 1 to 10 of 18

find a combination of numbers that equal a given sum

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 ...

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    12

    Default find a combination of numbers that equal a given sum

    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

  2. #2
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    7,150

    Default Re: find a combination of numbers that equal a given sum

    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

  3. #3
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    7,972

    Default Re: find a combination of numbers that equal a given sum

    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.

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    12

    Default Re: find a combination of numbers that equal a given sum

    Thanks Fazza the solver add-in did the trick!

    -Keith

  5. #5
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,924

    Default Re: find a combination of numbers that equal a given sum

    For future reference:
    Find a set of amounts that match a target value
    http://www.tushar-mehta.com/excel/te...ues/index.html

  6. #6
    New Member
    Join Date
    Mar 2008
    Posts
    13

    Default Re: find a combination of numbers that equal a given sum

    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.

  7. #7
    New Member
    Join Date
    Jun 2009
    Location
    Florida
    Posts
    1

    Default Re: find a combination of numbers that equal a given sum

    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.

  8. #8
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,924

    Default Re: find a combination of numbers that equal a given sum

    The page I referred to includes the Solver approach including a downloadable template.
    Quote Originally Posted by purdyk1 View Post
    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.

  9. #9
    New Member
    Join Date
    Jan 2010
    Posts
    17

    Default Re: find a combination of numbers that equal a given sum

    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?

  10. #10
    New Member
    Join Date
    Jan 2010
    Posts
    17

    Default Re: find a combination of numbers that equal a given sum

    I checked the MT website - some nice advice - though im too much of a noob to build macros - so more research for me

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com