Excel 2016 for Mac Solver horrible bug (example posted)

Good news! We fixed some issues with Solver to address reports of incorrect results that were being generated on Excel for Mac. The fix is available in build 15.28 (161025) or later. Please update your Office and let us know if you continue to encounter any issues.

Freya

Office Newsroom

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)

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.

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"

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.

Anyway, I hope this helps.

= = = = =

HTH   :>)

Dana