Solver or Other?

[Stan]

Should i use solver for this scenairo or try other ways?

Here i have 2 products: A and B
price of A $275
price of B $85

If i only have the total sales amount of A+B eg ($1165= 275*3 + 85*4), how
can i work back the quantity for each of the category?

Thx guys~

 

[JoeU2004]

Should i use solver for this scenairo or try other ways?
Here i have 2 products: A and B
price of A $275
price of B $85
If i only have the total sales amount of A+B eg ($1165= 275*3 + 85*4),
how can i work back the quantity for each of the category?

Solver does work for this simple example. I don't know how well it would do
with more variables. Here is one way to use Solver.

In A1 and A2, put 275 and 85. In C1, put the formula =B1*A1+B2*A2. In
Solver, set the Target Cell to C1 with Equal To ... Value of 1165. Set the
By Changing field to B1,B2. And add the following constraints: B1=integer,
B2=integer, B1>=0 and B2>=0.

 

[JoeU2004]

Simplification....

And add the following constraints: B1=integer, B2=integer, B1>=0 and
B2>=0

You can avoid the >=0 constraints by clicking Option and setting Assume
Non-Negative.


----- original message -----

 

[Dana DeLouis]

eg ($1165= 275*3 + 85*4)...

Hi. Just to add... With 1165, your one solution is {3,4}

Be aware that with totals like 9520, etc, you would have 3 solutions:
{0, 112}, {17, 57}, {34, 2}

Solver would return only 1 solution.

Note that it grows quickly: A total of 28050 would have 7 solutions, etc...
{0, 330}, {17, 275}, {34, 220}, {51, 165},
{68, 110}, {85, 55}, {102, 0}

= = =
Dana DeLouis

 

[Stan]

Thanks JoeU2004 & Dana DeLouis~

Dear Dana DeLouis,
Thanks for your sharing and remind for the mutiple answer issue.
BTW, how do you get those answers?!

Is there any ways to list out all possible answers?!
I just read your post - Subject: all possible combinations
(but seems this is not the same thing right?)

Stan

 

[Dana DeLouis]

I just read your post - Subject: all possible combinations

(but seems this is not the same thing right?)

Hi. Not so much Combinations. What you are solving are Linear
Diophantine equations.
Before running Solver, we note that in order for there to be a solution
at all, GCD(275,85) (ie 5) must divide into 1165. Which it does.
(Mod(1165,5) = 0)
If, for example, your total was 1166, then we know right away that there
are no solutions, and there would be no sense in running Solver.
Even if the total was 5, there is a least a solution.
-4*275 + 13*85 = 5

However, these have negative numbers, and not what you want.

BTW, how do you get those answers?!

If given a total of say 28050, we note that both 275, and 85, divide
into this number as an integer.( ie 102, and 330 respectively)
So, our first solution is {0,333}

Here is one way to get the family of solutions:

Sub Demo()
Dim a, b, x, y, t, d

a = 275
b = 85
x = 0
y = 330
d = WorksheetFunction.Gcd(a, b)

For t = 0 To 6
Debug.Print x + t * (b / d); ": "; y - t * (a / d)
Next t
End Sub

Returns:
0 : 330
17 : 275
34 : 220
51 : 165
68 : 110
85 : 55
102 : 0

Always an interesting subject. :>)
= = = = =
Dana DeLouis