How to use Excel's Goal Seek and Solver to solve for unknown variables

Excel is very capable when you have all the data you need for your calculations.

But wouldn't it be nice if you could resolve unknown variables ?

With Goal Seek and the Solver plugin, you can. And we'll show you how. Read on for a complete guide on how to solve a single cell with Goal Seek or a more complicated equation with Solver.

How to use Goal Seek in Excel

Goal Seek is already integrated into Excel. It is under the Data tab, in the And if analysis menu:

For this example, we will use a very simple set of numbers. We have three quarters of sales and an annual goal. We can use Goal Seek to figure out what the numbers need to be in the fourth quarter to achieve the goal.

As you can see, the current sales total is 114,706 units. If we want to sell 250,000 by the end of the year, how many do we need to sell in the fourth quarter? Excel's Goal Seek will tell us.

Here's how to use Goal Seek, step by step:

1. Click Data> Do-If Analysis> Seek Goals . You will see this window:
   
2. Put the “is equal to” part of your equation in the Set cell countryside. This is the number that Excel will try to optimize. In our case, it's the running total of our sales numbers in cell B5.
3. Type your target value in the Assess countryside. We're looking for a total of 250,000 units sold, so we'll put “250,000” in this field.
4. Tell Excel what variable to resolve for in the Switching cell countryside. We want to see what our sales should be in the fourth quarter. So we'll tell Excel to solve for cell D2. It will look like this when you are ready to go:
   
5. Hit OK To solve your goal. When it looks good, just tap OK . Excel will notify you when Goal Seek has found a solution.
   
6. Click OK again and you will see the value that solves your equation in the cell you chose for Changing Cells .
   

In our case, the solution is 135,294 units. Of course, we could have found that out by subtracting the running total from the annual goal. But Goal Seek can also be used on a cell that already has data in it . And that is more useful..

Please note that Excel overwrites our previous data. It's a good idea to run Goal Seek on a copy of your data . It's also a good idea to make a note of the copied data that was generated using Goal Seek. You don't want to confuse it with current and accurate data.

Therefore, Goal Seek is a useful feature of Excel. 16 Excel Formulas to Help You Solve Real-Life Problems. 16 Excel Formulas to Help You Solve Real-Life Problems. The right tool is half the job. Excel can solve calculations and process data faster than your calculator can. We show you the key Excel formulas and show you how to use them. Read More Let's take a look at a much more interesting tool:the Solver plugin.

What does the Excel solver do?

In short, Solver is like a Multivariate version of Goal Seek. . It takes a target variable and adjusts a number of other variables until you get the answer you want.

You can resolve to a maximum value of a number, a minimum value of a number, or an exact number.

And it works within constraints, so if a variable can't be changed, or can only vary within a specific range, Solver will take that into account.

It's a great way to solve for multiple unknown variables in Excel. But finding and using it is not easy.

Let's take a look at loading the Solver add-in, and then get started using Solver in Excel 2016.

How to load the Solver plugin

Excel does not have Solver by default. It's such an add-in that just like other powerful Excel features, Power Up Excel 10 Add-ins to Process, Analyze and Visualize Data Like a Pro Power Up Excel 10 Add-ins to Process, Analyze and Visualize Data Like a Pro Vanilla Excel Es awesome, but you can make it even more powerful with plugins. Whatever information you need to process, chances are someone has created an Excel application for it. Here is a selection. Read More Fortunately, it's already on your computer.

Go to File> Options> Plugins . Then click Go near Manage:Excel Add-Ins .

If this dropdown says something other than "Excel Add-ins," you'll need to change it:

In the resulting window, you will see a few options. Make sure the box next to Solver Plugin checked, and press OK .

You will now see the Solver button in the Analysis Data group tab:

If you've already been using the Data Analysis Toolpak How to Do Basic Data Analysis in Excel How to Do Basic Data Analysis in Excel Excel isn't meant for data analysis, but it can still handle statistics. We'll show you how to use the Data Analysis Toolpak add-in to run statistics from Excel. Read More If not, Solver will appear by itself.

Now that you've loaded the plugin, let's take a look at how to use it.

How to use Solver in Excel

There are three parts to any Solver action:the goal, the variable cells, and the constraints. We will walk through each of the steps..

1. Click Data> Solver . You will see the Solver Parameters window below. (If you don't see the Solver button, see the previous section on loading the Solver plug-in.)
   
2. Set the target of your cell and tell Excel your target. The target is located at the top of the Solver window and has two parts:the target cell and the option to maximize, minimize, or a specific value.
   
   If you select Max , Excel will adjust your variables to get the largest possible number in your target cell. Min it is the opposite:Solver will minimize the target number. Value of allows you to specify a specific number for Solver to search for.
3. Choose the variable cells that Excel can change. Variable cells are set with the Changing variable cells countryside. Click the arrow next to the field, then click and drag to select the cells that Solver should work with. Note that these are all cells That can vary. If you don't want a cell to change, don't select it.
   
4. Set constraints on single or multiple variables. Finally, we come to the constraints. This is where Solver is really powerful. Instead of changing any of the variable cells to whatever number you want, you can specify constraints that must be met. For more information, see the section on how to set restrictions below.
5. Once all of this information is in place, hit Solve to get your answer. Excel will update your data to include the new variables (so we recommend that you first create a copy of your data).

You can also generate reports, which we'll look at briefly in our Solver example below.

How to set constraints in Solver

You could tell Excel that a variable must be greater than 200. When trying different variable values, Excel won't go past 201 for that particular variable.

To add a restriction, click Add button next to the list of restrictions. You will get a new window. Select the cell (or cells) to constrain in the Cell Reference field, then choose an operator.

Here are the available operators:

• <= (Less than or equal to)
• = (equal to)
• => (Greater than or equal to)
• At t (must be an integer)
• compartment (must be 1 or 0)
• Everything different

Everything different It's a little confusing. Specifies that each cell in the range that you select Cell Reference It must be a different number. But it also specifies that they must be between 1 and the number of cells. So if you have three cells, you'll end up with the numbers 1, 2, and 3 (but not necessarily in that order)

Finally, add the value for the constraint.

It is important to remember that you can select multiple cells for the cell reference. If you want six variables to have values ​​greater than 10, for example, you can select all of them and tell Solver that they must be greater than or equal to 11. You don't have to add a constraint for each cell.

You can also use the check box in the Solver main window to ensure that all values ​​for which you have not specified constraints are non-negative. If you want your variables to become negative, uncheck this box.

An example resolution

To see how this all works, we'll use the Solver plug-in to do a quick calculation. Here's the data we're starting with:

In it, we have five different jobs, each of which pays a different rate. We also have the number of hours a theoretical worker has worked at each of those jobs in a given week. We can use the Solver plugin to figure out how to maximize the total payoff while keeping certain variables within some constraints.

Here are the constraints we'll use:

• No jobs it can drop below four hours.
• Job 2 must be more than eight hours .
• Job 5 must be less than eleven hours .
• Total hours worked must be equal to 40 .

It may be useful to write your constraints in this way before using Solver.

This is how we set it up in Solver:

First of all, note that I have created a copy of the table. so we don't overwrite the original, which contains our current working hours.

And second, see that the values ​​in the greater than and less than constraints are one higher or one lower than what I mentioned above. That's because there are no greater than or less than options. There is only greater than or equal to and less than or equal to.

Let's hit Resolve and see what happens.

Solver found a solution! As you can see on the left of the window above, our earnings have increased by $130. And all restrictions have been met.

To keep the new values, make sure Solvent Solver Solution is reviewed and beaten OK .

However, if you want more information, you can select a report from the right side of the window. Select all the reports you want, report to Excel if you want them described (I recommend) and click OK .

Reports are generated on new sheets in your workbook and give you information about the process the Solver plugin went through to get your answer.

In our case, the reports are not very interesting and there is not much interesting information there. But if you run a more complicated Solver equation, you can find useful reporting information in these new worksheets. Just click the + button on the side of any report to get more information:

Advanced Solver Options

If you don't know much about statistics, you can ignore Solver's advanced options and just run it as is. But if you're running large, complex calculations, you may want to examine them.

The most obvious is the resolve method:

You can choose between GRG Nonlinear, Simplex LP and Evolutionary. Excel provides a simple explanation of when you should use each one. A better explanation requires some knowledge of statistics. Learn stats for free with these 6 resources. Aprenda estadísticas gratis con estos 6 recursos. Las estadísticas tienen una reputación de un tema que es difícil de entender. Pero aprender del recurso correcto lo ayudará a comprender los resultados de la encuesta, los informes de elecciones y las asignaciones de su clase de estadísticas en ningún momento. Leer más y regresión.

Para ajustar configuraciones adicionales, simplemente pulse la tecla Opciones button. Puede decirle a Excel sobre la optimización de enteros, establecer restricciones de tiempo de cálculo (útil para conjuntos de datos masivos) y ajustar cómo GRG y los métodos de resolución evolutiva se basan en hacer sus cálculos..

Nuevamente, si no sabes qué significa esto, no te preocupes por eso. Si desea saber más sobre qué método de resolución usar, Engineer Excel tiene un buen artículo que lo expone para usted. Si quieres la máxima precisión, Evolutionary es probablemente una buena manera de hacerlo. Solo ten en cuenta que tomará mucho tiempo.

Búsqueda de objetivos y solucionador:llevar a Excel al siguiente nivel

Ahora que está cómodo con los conceptos básicos de la resolución de variables desconocidas en Excel, está abierto para usted un mundo completamente nuevo de cálculo de hojas de cálculo..

Goal Seek puede ayudarlo a ahorrar tiempo al hacer algunos cálculos más rápido, y Solver agrega una gran cantidad de poder a las habilidades de cálculo de Excel Cómo calcular estadísticas básicas en Excel:una guía para principiantes Cómo calcular estadísticas básicas en Excel:una guía para principiantes Microsoft Excel puede hacer estadísticas! You can calculate percentages, averages, standard deviation, standard error, and Student's t-tests. Read more.

Es solo cuestión de sentirse cómodo con ellos. Cuanto más los uses, más útiles serán..

¿Utilizas Goal Seek o Solver en tus hojas de cálculo? ¿Qué otros consejos puede proporcionar para obtener las mejores respuestas de ellos? Share your thoughts in the comments below!