Good news for those of you that use spreadsheets to do analytics: Google recently announced a Linear Optimization add-on for Google Sheets, and now Frontline Systems has released a free Solver add-on for Google Sheets that solves not only linear optimization problems, but nonlinear ones as well. It has roughly the same capabilities as the Solver App for Excel Online. If you know how to use Excel’s Solver, then you know how to use this. (Disclaimer: I participated in the development of both the Google Sheets and Excel Online apps during my tenure as CTO of Frontline. I think they are great.)

Here’s how to get started with the Solver add-on for Google sheets.

Step 1: Insert the Add-on. Create a new Google Sheet (for example by going to drive.google.com and clicking “New”). Then, under the Add-ons menu, click “Get add-ons..”. Search for “solver” and you will see both the Google and Frontline apps:

Click on the button next to Solver. (Hi Edwin!) Now “Solver” will appear under the Add-ons menu. When you click on it, a pane will show up on the right-hand side of your screen.

Step 2: Create an optimization model. You can use the task pane to define the variables, objective, and constraints of your optimization model. Clicking on the “Insert Example” button will paste a sample problem into your sheet. Here’s what it looks like: it’s a production planning problem where we want to determine the number of TVs, Stereos, and Speakers to build in order to maximize profit.

In the task pane on the right you can see that the profit cell (F13) has been selected as the objective we are maximizing. Similar to the Excel solver, you can define the constraints by clicking on them in the “Subject To” section.

Step 3: Solve. Clicking on Solve will call Frontline’s Simplex solver to solve your model on the cloud (specifically – Windows Azure…). The variables B3:D3 will be updated, as will any formulas that depend on those values. As you can see, profit goes up:

WINNING. If you fool around with the app you will see that you can solve models with arbitrary formulas, not just linear models. And it’s free! Go check it out.

## 5 thoughts on “Optimization In Google Sheets”

1. denis.akhiyarov@gmail.com says:

mot working for me, gets stuck on solve with no error output

1. Nathan, I am working with VBA solver code to execute solver for plenty of cases.
For the last two years I have been moving from Excel to Google sheets and I am working a lot with Google scripts what for me is amazing.
Now the only process I am doing in Excel is the execution of this VBA solver code. I am investigating if is possible to program nonlinear optimization in Google script in order to automate the execution of solver optimization. If is not possible I would like to know if there is a software that allow me to do this or to execute the nonlinear optimization function taking the information from MySQL and bringing back the results to MySQL.

2. Great addon! Could you add resume function to solver? Because the optimization process will stop after 5 mins (maybe) due to google’s policy (stop if calculation time >= 5mins)

3. How can I find someone who can do this for my business? I’m a small business owner and I do not have the time or resources to figure out how to do this for myself. 954-376-0053 jennifer@littlesphotography.com