+ Reply to Thread
Results 1 to 3 of 3

calculate the best combination of numbers

  1. #1
    Registered User
    Join Date
    01-19-2009
    Location
    Sweden
    MS-Off Ver
    Excel 2016 Office 365
    Posts
    25

    calculate the best combination of numbers

    Ok, so I don’t know if this is possible at all, but i’ll ask the question and let’s see:

    I would like to make some automatic calculations:

    From a arbitrary number, I need to find the best combination of other, pre-defined, numbers, as close to the arbitrary number as possible.

    My aim is to manually update one column with an arbitrary number, then make Excel calculate and fill the best combination of the pre-defined numbers.

    Pre-defined numbers are 350, 500, 700, 1000.

    Examples:

    If the arbitrary number is 865 - then I want to automatically calculate that 1x350 and 1x500 is the best combination. And rhen automatically, 1 goes in the column for 350 and 1 goes in the column for 500
    If the arbitrary number is 1412, I want the combination to be 2x700. 2 goes in the column for 700.
    And so on.

    I have no idea at all if thhos could be done. All help is appreciated!

    Screenshot for explanation, numbers are manually entered:

    Namnlös.png

  2. #2
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: calculate the best combination of numbers

    this appears to be a Knapsack Problem

    the solution is usually found by using VBA or Solver.

    im sorry, i dont have good example for you, but i hope that a search of the forum for "knapsack" leads you in the right direction.
    As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.

    And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]

  3. #3
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: calculate the best combination of numbers

    Not sure solver is the best tool for this, still I did setup three models. Solver_1 and Solver_2 are basically the same model. Model Solver_2 find the same result as you wanted but it uses a different combination
    of numbers so I made a small modification to Solver_3 to get the result you wanted.

    Combining numbers to a certain sum may yield a number of combination. Is there any particular reason you selected 2*700 to match the value 1412?

    Some time ago I wrote a macro for running GoalSeek in order find different factors in equations, could be this macro could be of use. Will check and post if this seems to be more suitable than Solver.

    To see the different model setups activate a sheet and select "Data" tab and click "Solver"

    Alf
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 08-23-2021, 02:03 PM
  2. [SOLVED] What is the formula to calculate the number of combination?
    By metrostar in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-16-2020, 02:12 AM
  3. [SOLVED] How to make the combination of numbers and total of that combination should be in range?
    By Darshan Shah in forum Excel Programming / VBA / Macros
    Replies: 38
    Last Post: 06-20-2020, 01:20 PM
  4. IF, AND, OR combination + how to calculate two deviations in same formula
    By JohnChoint in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-28-2014, 05:12 AM
  5. How to calculate total probability of loss from combination of probabilities
    By prr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-09-2009, 06:48 PM
  6. [SOLVED] calculate all possible combination from 10 variables
    By silvia in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-12-2006, 08:10 PM
  7. Replies: 0
    Last Post: 01-05-2005, 09:14 PM

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