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?
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
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?
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
