Sunday, April 20, 2014

How do they make a bug like this?

For a small project I needed to solve a very small QP model. As the input and output were in Excel, it made some sense to try Excel’s Solver. One of the constraints is:
image
The way to do this is to create a cell with the sum and then use Solver.SolverAdd to add a constraint. The complete call can look like:
Call Solver.SolverAdd(CellRef:=sumcell, Relation:=2, FormulaText:=1)
Unfortunately (for me) this did not work and I see:
image
After much experimentation I used instead:
Call Solver.SolverAdd(CellRef:=sumcell, Relation:=2, FormulaText:=0.999999999999999)
Now I see:
image
How in the world can such a bug be introduced? Some bugs make sense, but the logic of this one is difficult to fathom. I cannot but think there must be a statement somewhere:
if rhs=1 and (not interactive) and (user=’Erwin.Kalvelagen’) then
    call delete_constraint_to_confuse_user
Solver works correctly with a =1 equation when used from the GUI.
Well, found some more reports on this strange bug:
This bug is present in Excel 2010 and 2013 (32 and 64 bit).