#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 150
Default Solver or Other?

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~
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default Solver or Other?

"Stan" wrote:
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.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default Solver or Other?

Simplification....

I wrote:
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 -----

"JoeU2004" wrote in message
...
"Stan" wrote:
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.


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 690
Default Solver or Other?

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 wrote:
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~

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 150
Default Solver or Other?

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" wrote:

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 wrote:
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~




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 947
Default Solver or Other?

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



Stan wrote:
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" wrote:

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 wrote:
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~

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Solver Richard4926 Excel Discussion (Misc queries) 4 December 17th 07 09:28 PM
Help with Solver MBA STUDENT Excel Discussion (Misc queries) 2 November 20th 07 06:11 AM
Interesting Solver problem (Solver encounters an error) MrShorty Excel Discussion (Misc queries) 3 December 22nd 05 11:52 PM
Using Solver with VBA EggShell Excel Discussion (Misc queries) 2 August 22nd 05 07:06 AM
solver [email protected] Excel Discussion (Misc queries) 0 May 20th 05 06:34 PM


All times are GMT +1. The time now is 02:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"