# calculate the best combination of numbers

1. ## 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  Register To Reply

2. ## 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.  Register To Reply

3. ## 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  Register To Reply