number to fit the best

[ekkeindoha]

Good day,
Please help. I would like to get a formula for the following.
The boxes can take the folling weight as maximum.
(A) 50 (B) 45 (C) 30
If I give a total of 130 it should give me 2 X A and 1 X C.

Thanks

 

[Sam Wilson]

Hi,

If you have "50" in A1, "45" in B1, "30" in C1 and your target ("130") in A3
then the following works but it's a bit horrible:

=INT(A3/A1) & "*A+" & INT((A3-INT(A3/A1)*A1)/B1) & "*B+" &
INT((A3-INT(A3/A1)*A1-(INT((A3-INT(A3/A1)*A3)/B1)))/C1) & "*C"

 

[ekkeindoha]

Plse explain how to sort out with Scenario tool.
Thx

 

[Dana DeLouis]

number to fit the best?

Hi. You have {50,45,30}, and your solution to 130 is as given {2,0,1}

What if you have multiple solutions...say 180?
{0, 0, 6} or {0, 2, 3} or {0, 4, 0} or {3, 0, 1}

= = = = =
Dana DeLouis

 

[ekkeindoha]

The actual idea are to use the least boxes. Lets say A can take 50kg, B 45kg
and C 30kg. So I would like to use only 3 boxes as it works out on 2 x A and
1 X C.

Thx

 

[Eero]

You may try also this approach:

Suppose a total value is in A1, A value is in B1, B value is in C1 and
C value is in D1.

Then:

in B3 enter formula =A1
in C3 enter formula =MOD(B3,B1)
in D3 enter formula =MOD(C3,C1)

Finally, to get an output :

in B2 enter formula =INT(B3/B1) and copy it to right

 

[ekkeindoha]

Good day,

"Euro" thx it seems your short formula are excellent, but I'm still stuck
with a slight issue on this. If I do it like you say and enter the total
value as 60, it gives me
A B C D
60 68 58 28
0 1 0
60 60 2
(0.88) (1.03) (2.14)
As you will notice there are extra figures right at the bottom.The idea is
to explain what I would like to change.B=88% and C=103%. At the end of the
day it would be better for me to use B as there are only 12% open and with B
running 3% over, I must use another box because all must go and to loose 12%
are a better opton.

Sorry for all this issues.
Thx