Solver not obeying non-negative constraint

Ryan C

New Member
Joined
Oct 14, 2015
Messages
8
Hi Everyone,

I'm quite certain this is a bug and there's probably no fix however I thought it would be worth asking. My issue is that even though I have the "Make Unconstrained Variables Non-Negative" box ticked, it's still using negative variables and crashing the equation!
The solver works by changing two variable cells (Green) (that must both be positive) so that the two objective cells (Orange)are as close to zero as possible. The first objective is set to 'minimum' in the solver while the second is set to equal zero as a constraint. The equation I'm using cannot work with negative numbers and results in #NUM errors if they are used.

I'm running the solver through a macro, with a couple of if conditions to ensure the starting variables are positive.

Sub SolveForFlow()
If Range("C13").Value < 0.00001 Then Range("C13").Value = 0.01
If Range("C6").Value < 1 Then Range("C6").Value = 1
SolverReset
Application.Run "SolverAdd", "$C$21", 2, "0"
Application.Run "SolverOk", "$C$16", 2, 0, "$C$6,$C$13", 1, "GRG Nonlinear"
Application.Run "SolverSolve", True
End Sub

Every third or fourth time I run the macro it inputs negative numbers (as low as 4000 so far) into the variable cells and results in an error. I've tried manually setting constraints for the variable cells to be greater than 0.001 but it still uses negative values!!!

Does anyone know why Solver is going in clear contradiction of these constraints and how to fix it?



onnxrgA.png


In case anyone is wondering this solves for gravity flow rate through a full pipe using Colebrook-White and Darcy-Weisbach equations.
The two objectives are:
- Total head loss must equal Available head
- Both sides of the Colebrook equation must equal each other


Many Thanks,

Ryan
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Forum statistics

Threads
1,223,276
Messages
6,171,139
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top