Solver - Solving Non Linear Equation by linearizing the equation

[piku9290dgp]

Hello,

I have a problem in excel solver where I need to multiply two variable making it a non linear equation. Please help me by transforming it into linear equation

I have attached the excel sheet for reference

[Hydraulics]

The non-linearity comes from the choice to express the transportation costs as a product of a binary var that tells us if a site has been chosen, and a real var for the demand shipped. By the way, you don't need to set it to integer.

This is a very intuitive way to think about the problem, but not the only one. We want the demand to be zero when the site is not chosen, and free to take any value if the site is chosen.
Since we have a capacity constraint in row 88, we can build a matrix like D62:AA85 where, in each cell, we set this limit, and then constraint our vars to be less than it (if the constraint must hold for the sum in a column, it must also hold for each single variable).
What happens if the site is not selected? Demand must be zero, so here is the trick: multiply the max by the binary var. If the site is chosen we respect the first constraint, and OpenSolver is free to find the best value, if it isn't we will have a zero in the cell, and the real var must then be zero as well.

Adding this constraint we can remove the binary variable from the formulas in D62:AA85. Changing then the formula in AB62:AB85 to a simple SUM, we have a linear model. If I didn't got something wrong, the minimum cost should be $70.150.174.758,4.

Let me know if these hints are enough.

HTH,

Francesco

[piku9290dgp]

Thank you Francesco for explaining the hints. I understand the part where you mentioned set the limt and constraint our variable less than that. However, I could not understand by multiply the max by the binary variable. Appreciate if you can explain that again.

Also, it would help if you can attach the excel sheet to it.

Thanks again for your help.

[piku9290dgp]

Please check if I could apply your hint successfully

[Hydraulics]

Sure, here is the file. I think it's self-explanatory, let me know if you still have questions.

HTH,

Francesco

P.S. Total cost is $ 41.339.339.794,8 if you maximize total demand.

[piku9290dgp]

Thank you Francesco, This really works. Appreciate your help

[Hydraulics]

Glad I could help. Would you please mark the thread as solved?

Francesco