|
Basic Solver Problem
Hi There,
My Case: A1:A10 contains Unit volumes for the 10 items B1:B10 contains Unit price C1:C10 = A*B C11 = Total Price D11 = Target Price By just changing the Unit volumes I want to reach the Target Price. My constraint: Each Item should keep it's relative volume share. Easy enough to calculate it without Solver but...how do you determine that constraint with Solver??? Thanks for your insight, Brgds Sige |
Basic Solver Problem
Do you mean
(1) the percentage of total volume must stay same for each item or (2) the ranking of each item must remain unchanged? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Sige" wrote in message oups.com... Hi There, My Case: A1:A10 contains Unit volumes for the 10 items B1:B10 contains Unit price C1:C10 = A*B C11 = Total Price D11 = Target Price By just changing the Unit volumes I want to reach the Target Price. My constraint: Each Item should keep it's relative volume share. Easy enough to calculate it without Solver but...how do you determine that constraint with Solver??? Thanks for your insight, Brgds Sige |
Basic Solver Problem
Hi Bernard,
(1) the percentage of total volume must stay same for each item !!! E.G.: Item A= 25 units Item B= 50 units Item C= 125 Units Total = 200Units The relative share should remain unchanged: Being 25/200= 12.5% for A, 25% for B, 62.5% for C Brgds Sige |
Basic Solver Problem
There's really nothing to solve. If the %s have to remain the same, you
have only one variable and simple algebra will yield the answer. In your example, B=2*A and C=5*A. So, the only variable you have to play with is A. If the unit prices were Pa, Pb, and Pc, you would get the total price as Pa*A + 2*Pb*A + 5*Pc*A or A(Pa +2Pb + 5Pc). Given a target of Pt, solve for A to get A = Pt/(Pa +2Pb + 5Pc). The generalization to 10 items is similarly straightforward. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article .com, says... Hi Bernard, (1) the percentage of total volume must stay same for each item !!! E.G.: Item A= 25 units Item B= 50 units Item C= 125 Units Total = 200Units The relative share should remain unchanged: Being 25/200= 12.5% for A, 25% for B, 62.5% for C Brgds Sige |
Basic Solver Problem
Hi Tushar,
Sure is true ... though this is just a simplified example! There are other constraints on these items in my model, that I CAN model in Solver ... though modelling the items to keep the relative share beats me a bit. Brgds Sige |
Basic Solver Problem
Ok Tushar.
Thanks for the clarification. Best Regards, SIge Tushar Mehta wrote: In article .com, says... Hi Tushar, Sure is true ... though this is just a simplified example! There are other constraints on these items in my model, that I CAN model in Solver ... though modelling the items to keep the relative share beats me a bit. Brgds Sige None of the other constraints matter. The relative share requirement trivializes the model to that of a single variable. The other constraints can do nothing other than (a) make the solution infeasible, or (b) be non- binding. I'll repeat this one more time and then leave this discussion alone. By keeping the relative shares constant, you have only one truly independent variable. It's like writing 'by changing' variables: A1, A2, A3, ... subject to A2=2*A1, A3=5*A1, etc. Which means you can put in A2:An the formulas: =2*A1 =5*A1 ... That will bring the 'by changing variables' down to A1. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions |
Basic Solver Problem
By just changing the Unit volumes I want to reach the Target Price.
Hi. For larger models, here is a technique that is similar as Tushar's. A1:A3 has your small sample...25, 50,125 Make two range names: Total, Volume Total =your original sum (200) Now use a helper column to calculate relative ratios. B1 =A1/Total B2 =A2/Total etc... Now, the new volumes will be in C1:C3 C1 = Volume*B1 C2 = Volume*B2 etc... Have Solver adjust just the Volume, and the volumes in C1:C3 will adjust, with the ratios staying the same. As a technique, some like to drop the C1:C10 calculations, and for Total Price, use a formula like =SumProduct(NewVolumes, UnitPrices) again, it all depends.. Anyway, hope this helps. :) As a side note, your original "Total Price" without all the above might be: =SUMPRODUCT(OriginalVolumes/Total*Volume,UnitPrices) ....where Solver is only adjusting the Volume. -- HTH. :) Dana DeLouis Windows XP, Office 2003 "Sige" wrote in message oups.com... Ok Tushar. Thanks for the clarification. Best Regards, SIge Tushar Mehta wrote: In article .com, says... Hi Tushar, Sure is true ... though this is just a simplified example! There are other constraints on these items in my model, that I CAN model in Solver ... though modelling the items to keep the relative share beats me a bit. Brgds Sige None of the other constraints matter. The relative share requirement trivializes the model to that of a single variable. The other constraints can do nothing other than (a) make the solution infeasible, or (b) be non- binding. I'll repeat this one more time and then leave this discussion alone. By keeping the relative shares constant, you have only one truly independent variable. It's like writing 'by changing' variables: A1, A2, A3, ... subject to A2=2*A1, A3=5*A1, etc. Which means you can put in A2:An the formulas: =2*A1 =5*A1 ... That will bring the 'by changing variables' down to A1. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions |
Basic Solver Problem
Thank you Dana!
Sige |
| All times are GMT +1. The time now is 02:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com