Sorry for the tricky title, I will say a lot clearer.

There is a certain amount, say 1 000 000.

There are certain numbers (15-20), say 50, 200, 500, 150, 300, etc.

You need to go through not just these numbers that their sum was equal to the known value (1 000 000), and the desired amount of quantities, that is, so:

50***1000**+ 200***600** + ... = 1 000 000

That is, what essentially need a script or a hint how to do it that matches the specified formula with the known values and the known total and automatically selects the unknown factor of the mathematical expression (in bold).

(the multiplier will have a small number, up to 20)

Thanks in advance!

There is a certain amount, say 1 000 000.

There are certain numbers (15-20), say 50, 200, 500, 150, 300, etc.

You need to go through not just these numbers that their sum was equal to the known value (1 000 000), and the desired amount of quantities, that is, so:

50*

That is, what essentially need a script or a hint how to do it that matches the specified formula with the known values and the known total and automatically selects the unknown factor of the mathematical expression (in bold).

(the multiplier will have a small number, up to 20)

Thanks in advance!

asked July 2nd 19 at 17:47

1 answer

answered on July 2nd 19 at 17:49

It's very simple.

Put the Excel add-in**solver**.

In one column drive their numbers (A1, A2, etc.). In the next, in front of them (B1, B2, etc) - 1

At the end of the list, under the units (just easier to see) write the formula =A1*B1+A2*B2+...An*Bn

After that, you run the add-in**solver**.

In the field**to Optimize an objective function** indicative of the cell with the formula.

**To**: *Values* (a radio button) in the prescribed number (in your case, 1000000)

**Changing cell variable** - specify a range of cells with the units (B1...Bn)

If the desired coefficients as integers, it is necessary to configure restrictions in the box below...remind button**Add** in the dialog box, specify:

**The reference to the cell** range B1...Bn

**Condition** *(secondary cell)* - a

Button**OK**

Click the button**to Find a solution**

Put the Excel add-in

In one column drive their numbers (A1, A2, etc.). In the next, in front of them (B1, B2, etc) - 1

At the end of the list, under the units (just easier to see) write the formula =A1*B1+A2*B2+...An*Bn

After that, you run the add-in

In the field

If the desired coefficients as integers, it is necessary to configure restrictions in the box below...remind button

Button

Click the button

Thank you, the addition and the choice of multiplier is working. But, as I understand it, this action must necessarily put all the available numbers. It would be ideal to solve the problem by trying to make some extra numbers simply discarded, as in the range B1...Bn is a fraction number, even given the limitations. Maybe just due to the fact that it is necessary in the expression have to consider the entire range of cells. I understand that it's all in the formula. Thank you for the tip! - Sylvan.Harvey commented on July 2nd 19 at 17:52

Try to play the specified range of cells. You don't have to use all. Plus restrictions can add the condition "no more than 20". - sylvan.Pfannerstill commented on July 2nd 19 at 17:55

Can specific numbers to put...see - sylvan.Pfannerstill commented on July 2nd 19 at 17:58

Find more questions by tags Excel