Solver doesn't find a solution, but I *know* there is one, pls help

[dejhantulip]

Hey everyone!

I am stuck at this simple (I believe it is simple *sighs*) solver problem...

Basically I have a total amount of money in a contract which is cell F23 ($37,257,548.96) and I am trying to redistribute some quantities but maintaining the final TOTAL AMOUNT constant.

So I created a very simple solver model that aims to make the difference of the original contract (Cell F23) and the new contract (Cell N23) equal to ZERO, thus maintaing the total contract amount--makes sense right? And the solver should be changing cell P10 (which is a number that is being added to the quantity of ITEM 9, I do it this way because in the future I might want to try to change two or three items respectfully--always with the same goal: maintaining the total amount of the contract equal to cell F23.

I know that if the "changing cell" in solver is equal to the number 22, the model is solved. But I run the model and it doesn't converge to any answers... and I would assume this would be simple. Clearly I am doing something wrong and need some help trying to understand how to model and solve this.

Please help me out!
Thank you very much in advance!

Excel file:
WorkOrderSample.xlsx

 

[KRice]

I don't see any issues with your approach, and I encountered the same problems you mentioned. In my experience, the Excel Solver works well for some problem formulations and not so well or not at all for others. If that happens, you might want to investigate other optimization engines. For example OpenSolver (OpenSolver for Excel) is an open-source add-in for Excel that complements the native Solver.
When I loaded up the Add-In, it still recognized your original model configuration (change P10 with the objective to make P23=0). I then chose the COIN-OR Bonmin (Non-linear solver) engine, clicked Solve, and the result of P10=22 was found quickly. If you go this route, I would recommend downloading the "Advanced" version, which includes some additional optimization engines. Once the zip file is extracted, then in Excel, select File > Options > Add-ins, then click "Go" beside Manage Excel Add-ins and Browse your file structure to identify the just-extracted add-in. This will activate the add-in for the current Excel session. If you want to make the add-in available every session by default, you'll have to move the add-in to the default location where Excel expects the add-ins to be stored.

 

[dejhantulip]

Thank you very much KRice! Your explanation was on point! Thanks!!!

 

[i_nth]

The issue is caused by having the "Precision as displayed" option selected. This causes the values to be rounded as Solver changes the variable. Since rounding is a discontinuous process, Solver objects that the model is non-linear.
The "Precision as displayed" option is generarlly a bad idea to use at any time, and definitely shouldn't be used with Solver.

 

[dejhantulip]

Oh wow, interesting dear i_nth, the only reason why I keep that precision as displayed on the sheet is because i need the sum of the product (quantity * unit price) to be rounded to 2 decimals always and someone once suggested to use precision as displayed and forget about rounding every single calculated cell.

 

[KRice]

Interesting...thank you @i_nth. I just confirmed this was the issue. In case anyone goes looking, this setting, at least on Excel 2019, is found under File > Options > Advanced, then scroll to "When calculating this workbook"...and there can be found the "Set precision as displayed" option button.