+ Reply to Thread
Results 1 to 8 of 8

combination of these numbers would sum

  1. #1
    Registered User
    Join Date
    09-21-2009
    Location
    AZ
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    12

    Thumbs up combination of these numbers would sum

    I have a list of say 20 numbers. The total sum could be anything - i need to find a certain sum using an unknown combination of the numbers. How would I do that?

    Ex:
    ($422.00)
    ($1,191.39)
    ($391.13)
    ($510.00)
    ($96.35)
    ($1,931.00)
    ($504.00)
    ($738.27)
    ($2,604.38)
    ($1,728.99)
    ($9,247.20)
    ($43.00)
    ($5,695.46)
    ($1,191.39)
    ($5,614.24)
    ($1,365.00)
    ($504.00)
    ($1,709.82)
    ($8,088.88)
    ($16.22)
    ($59.50)
    ($9,282.40)

    What combination of these numbers would sum to ($7,921.29)?
    Last edited by alisapooh; 09-22-2009 at 12:29 PM.

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: combination of these numbers would sum

    You can likely do this using Excel Solver
    How to use Excel Solver

  3. #3
    Registered User
    Join Date
    09-21-2009
    Location
    AZ
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    12

    Re: combination of these numbers would sum

    that looks like having to buy something

  4. #4
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: combination of these numbers would sum

    hi,

    I don't have knowledge to offer a sound solution, but I know that this can be referred to as a "knapsack problem" so you may find something useful if you include the phrase as a search term.

    The below link shows a similar problem but with more possible numbers that was used as a challenge of the month on an Excel website. The massive thread includes a number of approaches, but from memory the winning result isn't actually published in the thread (I think it is elsewhere on the website though)...
    http://www.mrexcel.com/forum/showthread.php?t=16533

    Sorry I can't be of more help, maybe one of the more knowledgeable members can...
    Rob
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

  5. #5
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: combination of these numbers would sum

    that looks like having to buy something
    They do offer products, but there is also a great tutorial, for free, on using Solver in Excel. Take another look.

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: combination of these numbers would sum

    heres how its done
    i got this from ozgrid some way back
    once solver is run the values in col b corresponding to the 1's in col c
    will give your total
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: combination of these numbers would sum

    This is Tushar Mehta's solution:
    Please Login or Register  to view this content.
    Running with your data gave this result:
    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  8. #8
    Registered User
    Join Date
    09-21-2009
    Location
    AZ
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    12

    Smile Re: combination of these numbers would sum

    martindwilson - THANK YOU!!!! That is exactly the instructions I needed!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1