Excel 2016 for Mac Solver horrible bug (example posted)

Hi.  If I didn't make a mistake here, just to let you know:

Solver for Windows:

Solver for Mac with starting values of 0

Same 375,000 and all constraints satisfied.

Hi Dana,

Thanks for the reply. I just had a number of other people solve this under Mac, and they are all getting zeros when starting with zeros (under Mac). Are you using Excel 2016 for Mac?

  Dan

Hi Dana,

Sorry about the slow reply. First off, thank you for spending time on this issue! As it turns out, you are right in that rewriting the LP to have constants in the right-hand side does return the optimal value. But this is not really a fix for the underlying problem, which is that Solver should really be returning the optimal value for an LP *irrespective of how the constraints are written*. In other words, as long as we're dealing with a linear program solver through Simplex, there should be no ambiguity about whether we're getting the optimal value... 

I added some quick replies to your other comments below. But my main take-away from this experience -- and from having taught the first class today where a lot of students were running Solver in Excel 2016 for Mac -- is that it is *completely unreliable*. It is a real pity what Microsoft has chosen to do with Solver, and not just on Mac! There are actually serious bugs under the Windows version as well, dating back to the time when Frontline used to manage it, particularly around linearity testing and parsing nonlinear expressions (I can give you more details if you are curious). At this point, I will probably be recommending all my students to just directly install OpenSolver, since they need something more powerful for projects, anyway.

Thanks again for spending time on this!

  Dan

Yes, I used the Mac to arrive at the conclusion that your model does not have 1 optimal solution!

Notice that "Super  Gas" and "Crude 3" can slide along 0-500, and "Reg Gas" can slide along 3500-4000 (inversely, with changes to the other mix).   All at Optimal value of: 375,000

I am not an expert, so here are just my observations.

Solver for Windows has been around for a long time, and Solver for Mac is rather "new"

In Solver models, and Op research as well, it is never good programming practice to place functions on the Right-Hand-Side  (RHS)

I very much agree with your comment from a "good practice" perspective. I actually also recommend my students to have the right-hand sides as parameters in the model, which makes it a bit easier to interpret sensitivity reports. But from the standpoint of solving the model, it should make absolutely zero difference how one writes the constraint -- everything on the left, everything on the right, some terms on the left and some on the right... Any serious parser would transform the problem into a standard form anyway before calling the Simplex algorithm, so this should be a non-issue. And that used to be a non-issue with older versions of Solver before Excel 2015/6 for Mac.

Solver for Windows has improved a lot over the years.  The program use to give up very quickly at the first sign of confusion.  It's been a lot better the last 2 or so revisions.

Pehaps... I have been using it for about 9 years now (primarily in teaching, never in research), and I have not noticed major improvements. But I could be mistaken, since I was not really pushing its limits in any way. But are you referring to using the Simplex algorithm or using GRG Nonlinear?  Again, there really shouldn't be much "confusion" with Simplex. If the problem is linear, you are guaranteed to have a global optimum, so a properly implemented Simplex routine should work just fine.

As my guess, the reason for the Mac confusion is that Solver is using "Finite Differences" to calculate a derivative to make its best guess to the RHS.   When it uses the new values, the RHS has jumped and changed values.  It then repeats the process.  The RHS target is always moving.  It's a dog chasing it's tail.   Solver for Mac, my belief, is that there is not enough logic in the program yet to reduce the confusion.  It will quit easily, and just go back to its initial starting values by default.  (Your all 0's)

Well, the Simplex algorithm does not actually rely on any finite differences (or derivatives...) The basic operations are essentially matrix multiplications and inversions, so I doubt that what you are saying is the root cause for the problems. But then again, I am also left guessing as to exactly what Microsoft (or Frontline for that matter) actually bothered to implement under the hood.

It was apparent to me how to fix your Mac Model.

You have 3 constraints <= 5000.   The 5000 is fixed, so we can leave that alone.

You have 7 other constraints entered using 2 constraints into Solver.

The 7th constraint is ok, but we will change that to be consistent.

The quickest solution for your model is to delete the 3 ">=" and 4 "<=" constraints.

Go ahead and keep your constraint area for readability, but we will not use it.

Make another column for constraints.   My preference is to use F(x) >= 0, so that's what I'll use here.

For your first 3 constraints, we will use the formulas you have, but make it:

= LHS - RHS

For the last 4, we will use

= RHS - LHS.

So now, we will enter just 1 constraint for all 7.

New 7 >= 0

Because the model is written slightly better, Mac Solver won't get confused and quit.

I quickly get the "other valid solution"

That was a nice guess / finding! I actually did not spend time trying to figure out how to make it work, simply because it *should have* worked in the first place...

One "could argue" that the Mac solution is slightly better, although they are equal.

Mac is suggesting making 5 items instead of 6.

So, we are saving wear and tear of making a 6th item (super gas/ crude3)

There is also more man-power setting up a 6th manufacturing item.

You also may have higher shipping costs for shipping a 6th item at the low 500 volumn.

Perhaps save money by shipping 1 item at 4000 volumn.   Etc.

Just for education, your model looked good to test the "Math" differences based on the inefficient model.

You have 1 columns of Sumproduct(), and another column of using "SUM"

I might have removed all 14 cells, and  just used 7.  Here's how I would have written the 7 constraints.

It's basically the same math:

=SUMPRODUCT((B12:D12),(H4:J4)-10)

=SUMPRODUCT((B13:D13),(H4:J4)-8)

=SUMPRODUCT((B14:D14),(H4:J4)-6)

=SUMPRODUCT(B12:D12,1-(H5:J5))

=SUMPRODUCT(B13:D13,2-(H5:J5))

=SUMPRODUCT(B14:D14,1-(H5:J5))

=D28-B28

I would have used 1 constraint:

All7  >= 0

When I do it this way on the Windows version, I get the same solution as the Mac because the Excel math is a little different.

I think the problem is indeed degenerate, i.e., there are multiple optimal solutions. One could argue what constitutes a "better" formulation (fewer variables / constraints, etc etc), but the key problem here is that if Solver actually had a proper parser -- i.e., something that reads the model and reformulates the problem before passing it to the actual algorithm -- then none of these issues should exist. And certainly they should not exist for the kind of toy model that was implemented in that spreadsheet...

Anyway, I hope this helps.

= = = = =

HTH   :>)

Dana

It does help. Thanks again.

>> I am not an expert, so here are just my observations.

>> Solver for Windows has been around for a long time, and Solver for Mac is rather "new"

>> In Solver models, and Op research as well, it is never good programming practice to place functions on the Right-Hand-Side  (RHS)

>> Solver for Windows has improved a lot over the years.  The program use to give up very quickly at the first sign of confusion.  It's been a lot better the last 2 or so revisions.

>> As my guess, the reason for the Mac confusion is that Solver is using "Finite Differences" to calculate a derivative to make its best guess to the RHS.   When it uses the new values, the RHS has jumped and changed values.  It then repeats the process.  The RHS target is always moving.  It's a dog chasing it's tail.   Solver for Mac, my belief, is that there is not enough logic in the program yet to reduce the confusion.  It will quit easily, and just go back to its initial starting values by default.  (Your all 0's)

>> It was apparent to me how to fix your Mac Model.

>> One "could argue" that the Mac solution is slightly better, although they are equal [...]