How to find the sum of the quantities of numbers for a specific amount (Excel) (Numbers)?
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)
sylvan.Pfannerstill 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