Solver De-bugging w/ Basic Product Ordering Table

**rpicc** · 05-27-2019, 08:09 PM

Hi all! Greenhorn here. Hoping for some help. : )

I'm attempting to use Solver to optimize procurement cost in ordering products with "x", "y", and "z" characteristics to meet a given demand. i.e., solving for lowest total cost (cell O30) while constraining the total order within the upper and lower bounds.

Where I seem to be getting stuck is that Solver is a) failing to find consistent solutions, and/or b) finding no solution when obvious ones do exist (when found manually).

Attaching the sheet here. Any insight would be greatly appreciated!

Thanks!

**MrShorty** · 05-28-2019, 11:24 AM

The Solver algorithms in my older version don't like "step" functions -- like the ROUNDUP() function in column H. So I took out the ROUNDUP() function in column H (figuring I can roundup after Solver completes its run), and ran your Solver model with no further changes. Even with different starting values, it consistently came to the same minimum. I did not understand your overall thought process, but that allowed Solver to run and find a solution. You might try that and see how it fits into your overall problem. Or you might look at other ways to modify the spreadsheet/solver model to get an objective function that Solver can more easily solve.