- Keyboard shortcuts in Excel. See the full list of keyboard shortcuts, organized into logical categories. Customize the ribbon and toolbars. Rearrange, show, and hide tabs on the ribbon. Fixes or workarounds for recent issues in Excel for Mac. See a list of known issues as well as potential workarounds.
- Click the Solver command button in the Analyze group at the end of the Ribbon’s Data tab. Excel opens the Solver Parameters dialog box. Specifying the parameters to apply to the model in the Solver Parameters dialog box. Click the target cell in the worksheet or enter its cell reference or range name in the Set Objective text box.
- In Excel for Mac 2011: Click the Data tab, under Analysis, click Solver. Click Load/Save, enter a cell range for the model area, and then click either Save or Load. When you save a model, enter the reference for the first cell of a vertical range of empty cells in which you want to place the problem model.
Starting with Excel 2011 Service Pack 1 (Version 14.1.0), Solver is once again bundled with Microsoft Excel for Mac. You do not have to download and install Solver from this page - simply ensure that you have the latest update of Excel 2011 (use Help - Check for Updates on the Excel menu). To enable Solver, click ‘Tools’ then ‘Addins’.
I just recently delivered a course on quantitative techniques to investing. Everything went as planned until I was on the topic of portfolio optimization. And something unexpected cropped up.
Well, I was illustrating how to use Microsoft Excel and Solver to perform a simple portfolio optimization comprising 2 assets. Prior to this, I made sure it works on my PC, my wife’s MS Surface, my friend’s Windows-based laptop and MAC Book.
Windows checkbox – TICKED.
![With With](/uploads/1/1/8/3/118399721/372260124.jpg)
Excel Solver On Mac - Microsoft Community
MAC checkbox – TICKED.
Sounds like all grounds covered? Or at least that is what I thought.
MAC Excel Solver Unable to Find The Solution
But as it turns out, my students running on MAC laptops cannot get the same results that I did. Somehow, their Solver is not giving them the correct solution. It came up with a sub-par solution that violates the constraints I set. So I ended up spending the next 10 minutes troubleshooting with a few of them to see what went wrong. But all of them were following my instructions correctly. The parameters are all in place. In fact, I even rekey them in myself.
So the verdict then? I don’t have the SLIGHTEST CLUE why it didn’t work! And a random thought crossed my mind: “Maybe Microsoft is out to screw Apple users???”
Then at the 'Destination Select' step, choose the option 'Install for all Users of this computer.' Step 4: Follow the steps as you're guided through the installation process. Spectrasonics for mac. Make sure to review the Installation Info and Licensing FAQ.
After the course end, I call forth the POWER of GOOGLE to see if others encountered the same issue. If it happens to all the MAC users in my class, then this is definitely not an isolated case. And I am right. It seems like many other MAC users experience various problems using Solver. Most of the complaints appear to come from users of Excel 2016. But some of my students were using the latest Excel 2019 version. It is inconceivable that such an issue remains after so many years! And the best part is that there don’t seem to be any viable solution out there.
How to FIX this problem?
It seems like a lost cause unless I code up my own “Solver”. That is until I chance upon a forum where someone mentioned that MAC Solver does not handle constraints well if you put variables on the righthand side of the equation. When I first read it, I was kind of dubious. My thought was “Huh? No way it can be that simple.” But no harm trying, I am not going to lose anything.
Alright, so what do I mean when I say “variables on the righthand side of the equation”? Let’s use an example.
The Optimization Setup
Objective: Minimize Portfolio Variance
By Changing: Weights of the assets (S&P 500 and TLT)
Constraints:
1. All weights must sum up to 100%.
2. Marginal Risk Contribution (MRC) of each asset must be equal.
3. All weights must be positive.
On my Windows Desktop, this is how I set things up in Solver.
Windows Excel Solver Set Up
Note how I expressed my constraint on Marginal Risk Contribution (MRC): $F$37 = $G$37. The righthand side of this equation is a variable. This works perfectly in a Windows-based system but flopped in many of those who use MAC.
So how do I fix this? All you need is a little tweak. I defined in Cell H37 the formula: F37-G37 i.e. the MRC of S&P 500 minus MRC of TLT. So if you want them to be equal, this cell has to give you a ZERO.
So I can now re-express the same constraint in Solver as $H$37 = 0. In that case, my right-hand side of the equation is now a CONSTANT and not a VARIABLE. Abracadabra, now the MAC EXCEL SOLVER WORKS!
For Those On Mac, You Can Try This Out
Please don’t ask me why it has to be done this way. And I do not know if this works across all MACs as well. I don’t have one myself. For work and heavy lifting purposes, I have always relied on my good old Windows-based PC (no offense to MAC fans out there). But I have asked friends whose MAC Solver failed using the Windows setup to test the new MAC set up. And so far, it works. Thus, if you also run into the same issue, no harm trying this out to see if it works for you.
In How to Get More out of Excel Solver I covered techniques to enhance your model and empower your analysis. One of the topics I received the most questions on is the automating the solver to solve for multiple values.
There was a brief explanation in the previous article, but nothing dedicated to how to implement it from start to finish. In this article we’ll go in depth from start to finish covering the concepts and methods step by step. By the end you’ll be able to automate any of your own personal models and scale your analysis.
To get the most out of this article you should be familiar with the basics of Excel Solver. If you need to brush up, check out the latest webinar by this author, Jacob Fairclough, Excel Solver: Optimize Your Budgets Across Campaigns.
How we’ll do it
We’ll leverage the macro recorder in Excel to handle most of the actual code. The record macro tool will allow Excel to convert our manual steps into VBA code.
We will then identify the key pieces of code to change. Then we will create a loop to dynamically select and replace these values and run the solver again.
This will be semi-technical but through this style of development we can focus on the process and the concepts instead of focusing on how to write code.
Setting up the macro recorder
The macro record functionality is found in the developer tab in the ribbon. This is not available by default but is easy to implement. If you navigate to your ribbon options you will see a checkbox for the developer tab.
The screenshot below is for Mac but PC based Excel is similar.
If you have not installed the solver add-on you can install it via the Add-ins menu. The Microsoft support site has instructions for all platforms, Load the Solver Add-in in Excel.
Recording the macro
Now that the set up is complete we we are ready to record! Set up your workspace appropriately with all your formulas and references. You’ll want to set up everything to the point before you build the solver model.
Make your way back to the developer tab on the ribbon. You will see a piece of paper with a red dot. Click on that button to open the recorder.
Give your macro a name and fill in the description if you’d like.
Once you hit Ok the recorder will begin.
Go through the steps of setting up your solver and solve it. Once this is complete go back to the developer tab and stop recording. It’ll be the same location as the button you used to start the recording.
Now that we have the base code we can clean it up and prep it for the next steps.
Clean up the code
Now we can edit the code, remove any unnecessary parts, and make sure we are only using the essential pieces of the macro recording.
You can edit the code through Excel’s VBA editor. To access your recorded code click the Macros button.
Navigate to your macro in the new menu and click edit.
Now that we have our code editor open we can start making tweaks.
Removing Unnecessary Code
If you happened to click through a bunch of other things while recording you can clear those out now and be left with something similar to below. If there is a lot of extra code and you are worried about breaking it, you can always rerecord.
Setting up the solver references
Excel Solver Macbook
One last step before we move on, go to Tools > References and select Solver. If you don’t do this the package will not load in the macro and you’ll get an error.
You can now change your parameters open the macros menu, select your macro, hit run and it will update the results.
Let’s line up the code against our solver model. You’ll see how it lines up. The language is different but you can piece together what it all means.
Next we’ll isolate the parts we need to change and wrap up our automation.
Rinse and repeat – building the loop
Now that we have a working solver. We need to repeat that multiple times. Let’s put an example together and cement the plan.
We started with a budget of $1,000 in the first model but we want to do the same thing for $100 increments up to $2,500.
Let’s dive in! We have a list of values in column B and our output in column C. If we wrote out the process it would look like,
- Set total spend to the value in B10.
- Solve the model.
- Place the output in C10.
- Choose next B value.
- Set the output in the C row.
- Repeat steps 2-5 for all values in B.
Starting the loop
Loops are coding constructs that repeat an action until hitting a logical end point. A loop will come in handy here to repeat the same process, running the solver, on every value until we reach the end of the value.
For this example we’ll define the end point by the number of rows. B10:B25 has 16 values.
We’ll start our loop and wrap it around our solver code. We’ll set up a variable to hold the loop number (i) and go through each value, solving the loop each time.
Resetting the solver
We’ll add another more condition at the start of our code. Since we want to rerun the solver each iteration, we’ll reset it each loop. This will clear the settings and start a fresh model.
We can do this via SolverReset.
Updating cell references
We now solve sixteen times but need to update our cell references. While we originally referenced B10 we need to move down one cell each run to update the limits. We can do this via the offset function. We can update “$B$10” to reference the cell then offset by i rows (0 on the first run, 1 row on the second, and so on). Each time the loop runs, i increases by one.
Collecting the output
The output will change each time so we’ll want to save it. We can follow the same process above but offset from C10 and set it equal to the value of the solved model. Then the loop starts over.
We’ll call C10 offset it by the appropriate number of rows then paste the value of our output.
Sidestepping the solver notifications
Let’s save ourselves a headache and not learn by example for a moment. Remember when you ran the solver and that menu popped up asking if you’d like to accept the solution? That will pop up every time if you don’t disable it. Imagine if you set up a loop with 100’s of values, you’d have to click accept every time!
Go ahead and add the following lines after your solver code and add (TRUE) to SolverSolve. This will mimic you accepting the changes.
Now we have the finished code!
Up and running
We can now run the code by selecting it from the macro list and running. If you’d like to use your workbook as a template you can assign the macro to a button for ease of use. You can find these in the developer tab.
Download Solvertable
You can also add any comparison graphs. In this case we want to compare the rate of spend growth versus the conversion volume growth. This example isn’t interesting but real data can help reveal efficiency breakpoints
Excel With Solver For Mac Download
Conclusion
Excel Solver With Macro
We covered quite a bit! Congratulations on getting your automated solver up and running. The example we covered was overly simplistic, but you can take the same concepts and apply it to any solver model.
This can save an enormous amount of time for models you need to solve multiple times and encourage users to experiment with different scenarios. Flash player for mac not working.
Solver In Excel (Examples) | How To Use Solver Tool In Excel?
If you want to use Excel Solver to help optimize digital advertising budgets, check out the latest webinar by this author, Jacob Fairclough, Excel Solver: Optimize Your Budgets Across Campaigns.