Add-in search for solutions and selection of several Excel parameters. Finding a MS EXCEL solution

Twitter

Microsoft Excel Add-in "Solution Search" is a powerful tool for finding solutions and is used in solving optimization problems. The solution search procedure allows you to find the optimal value of the formula contained in the cell, which is called the target cell. The procedure works on a group of cells that are directly or indirectly related to a formula in the target cell.

Brief description of the add-on

To obtain a given result using a formula, the procedure changes the values in the influencing cells. Value constraints are used to reduce the range of values used in the model. The Solution Finder add-in is a standard Microsoft Office Excel add-in and is available immediately when you install Microsoft Office in general or Microsoft Excel in particular.

How to install the add-on?

The Find Solution add-in can be installed in two ways. Standard add-ons, such as "Solution Search" and "Analysis Package" are installed along with MS Office or MS Excel. If the standard add-in was not installed during the initial installation, you must run the installation process again. Let's look at installing the "Search for Solution" add-in using Microsoft Office 2010 as an example. In versions 2003 and 2007, everything is done in the same way.

So, launch the installation disk with the MS Office 2010 application package and select the “Add or remove components” option.

Next, click the “Continue” button, in the installation parameters we find the Microsoft Excel application, in the components of this application we find the “Add-Ins” section, select the “Solution Search” add-on and set the “Run from my computer” option.

Again, click the “Continue” button and wait until the add-on is installed.

How to connect the add-on?

Before use, you must first enable the add-in by checking the box in front of its name in the list of available add-ons in the Add-ons dialog box.

Invoking this window varies slightly depending on the version of the application. This is written in detail in a separate article " How to install the add-in for Excel 2003/2007/2010? " with screenshots for each of the three versions of the Excel application, so I will not repeat myself. Yes, I’ll just add a few words about the second method of installing this add-on. You can find a file on the Internet with the name Solver.xla(this is the “Search for a solution” add-on) and install it in accordance with the description in the link above.

One of the most interesting features in Microsoft Excel is the Solution Finder. However, it should be noted that this tool cannot be considered the most popular among users in this application. But in vain. After all, this function, using the initial data, by enumeration, finds the most optimal solution of all available ones. Let's find out how to use the Find Solution function in Microsoft Excel.

You can search for a long time on the tape where the Solution Search is located, but still not find this tool. Simply, to activate this function, you need to enable it in the program settings.

In order to activate the Search for Solutions in Microsoft Excel 2010 and later versions, go to the “File” tab. For the 2007 version, click on the Microsoft Office button in the upper left corner of the window. In the window that opens, go to the “Settings” section.

In the parameters window, click on the “Add-ons” item. After the transition, at the bottom of the window, opposite the “Management” parameter, select the “Excel Add-ins” value and click on the “Go” button.

A window with add-ons opens. We put a tick next to the name of the add-in we need - “Search for a solution.” Click on the “OK” button.

After this, a button to launch the Search for Solutions function will appear on the Excel ribbon in the “Data” tab.

Preparing the table

Now that we have enabled the feature, let's understand how it works. The easiest way to illustrate this is with a concrete example. So, we have a table of wages for company employees. We should calculate the bonus of each employee, which is the product of the salary indicated in a separate column by a certain coefficient. At the same time, the total amount of funds allocated for the bonus is 30,000 rubles. The cell in which this amount is located is called target, since our goal is to select the data exactly for this number.

We have to calculate the coefficient that is used to calculate the bonus amount using the Search for Solutions function. The cell in which it is located is called the desired one.

The target and search cells must be related to each other using a formula. In our specific case, the formula is located in the target cell and has the following form: “=C10*$G$3”, where $G$3 is the absolute address of the desired cell, and “C10” is the total amount of wages from which the bonus is calculated employees of the enterprise.

Launching the Find Solution Tool

After the table is prepared, being in the “Data” tab, click on the “Search for a solution” button, which is located on the ribbon in the “Analysis” tool block.

A settings window opens in which you need to enter data. In the “Optimize objective function” field, you need to enter the address of the target cell where the total bonus amount for all employees will be located. This can be done either by typing the coordinates manually, or by clicking on the button located to the left of the data entry field.

After this, the parameters window will collapse, and you can select the desired table cell. Then, you need to click again on the same button to the left of the form with the entered data to expand the parameters window again.

Under the window with the address of the target cell, you need to set the parameters of the values that will be in it. This could be a maximum, a minimum, or a specific value. In our case, this will be the last option. Therefore, we put the switch in the “Values” position, and in the field to the left of it we write the number 30000. As we remember, this number, according to the conditions, is the total amount of the bonus for all employees of the enterprise.

Below is the “Changing Variable Cells” field. Here you need to indicate the address of the desired cell, where, as we remember, the coefficient is located, by multiplying the basic salary by which the bonus amount will be calculated. The address can be entered in the same way as we did for the target cell.

In the “Subject to restrictions” field, you can set certain restrictions for the data, for example, make the values integer or non-negative. To do this, click on the “Add” button.

After this, the window for adding restrictions opens. In the “Link to cells” field, enter the address of the cells for which the restriction is being introduced. In our case, this is the desired cell with the coefficient. Next, we put down the required sign: “less than or equal to,” “greater than or equal to,” “equal to,” “integer,” “binary,” etc. In our case, we will choose the greater than or equal sign to make the coefficient a positive number. Accordingly, in the “Limitation” field we indicate the number 0. If we want to set up another limitation, then click on the “Add” button. Otherwise, click on the “OK” button to save the entered restrictions.

As you can see, after this, the constraint appears in the corresponding field of the solution search parameters window. You can also make variables non-negative by checking the box next to the corresponding parameter just below. It is advisable that the parameter set here does not contradict those that you specified in the restrictions, otherwise a conflict may arise.

Additional settings can be specified by clicking on the “Options” button.

Here you can set the accuracy of the constraint and the limits of the solution. When the required data has been entered, click on the “OK” button. But, for our case, there is no need to change these parameters.

After all the settings are set, click on the “Find solution” button.

Next, the Excel program performs the necessary calculations in cells. Simultaneously with the output of the results, a window opens in which you can either save the solution found or restore the original values by moving the switch to the appropriate position. Regardless of the option chosen, by checking the “Return to options dialog box” checkbox, you can go back to the solution search settings. After the checkboxes and switches are selected, click on the “OK” button.

If for some reason the results of the search for solutions do not satisfy you, or when calculating them the program generates an error, then, in this case, we return, using the method described above, to the parameters dialog box. We are reviewing all the entered data, as there may have been an error somewhere. If the error was not found, then go to the “Select solution method” parameter. Here you can choose one of three calculation methods: “Searching for solutions to nonlinear problems using the OPG method,” “Searching for solutions to linear problems using the simplex method,” and “Evolutionary search for solutions.” By default, the first method is used. We try to solve the problem by choosing any other method. If unsuccessful, try again using the last method. The algorithm of actions is the same as what we described above.

As you can see, the Find a Solution function is a rather interesting tool that, if used correctly, can significantly save the user’s time on various calculations. Unfortunately, not every user knows about its existence, let alone knows how to work with this add-in correctly. In some ways this tool resembles the function “Selection of parameter...”, but at the same time, has significant differences with it.

The Find Solutions add-in in Excel 2007 is not standard. It is intended for complex calculations where there is more than one unknown. Therefore, it is not included in the normal set of program parameters. But if there is a need for it, then it can offer the user effective work and high productivity.

What is Search for Solutions?

Search for solutions in Excel 2007 is a program add-in. This means that the typical manufacturer configuration does not install this package. It must be downloaded and configured separately. The fact is that most often users do without it. The add-in is also often called the “Solver” because it is capable of performing accurate and fast calculations, often no matter how complex the problem is presented to it.

If the version of Microsoft Office is original, then there will be no installation problems. The user needs to make several transitions:

Options→Tools→Add-Ins→Manage→Excel Add-ins.

A window will open with a transition button. After clicking on it, a list of all proposed add-ons, both installed and unused, will appear on the screen. Now you need to find Search for solutions, then check the box. The tool is activated and can be used at any time.

Why do you need a Solver?

What can you use Solutions Finder for in Excel 2007, and is it worth installing at all? When the user has a problem that depends on several parameters, the add-in will select solutions to the problem in accordance with the initial data. These could be a variable, an unknown, or, for example, a final value. That is, the user can have initial characteristics and an answer, and the program will select the solution and provide a formula.

Thus, through the add-in you can find:

Successful distribution of work resources in order to achieve maximum profit in the course of the activities of a company or a separate department or branch.
Distribution of investments with minimized risks.
Solving problems where there is more than one unknown (several answer options will be offered, from which the user will select the most suitable one).
Saving and loading the solution model. The best option for employees who are forced to constantly change their computer or laptop.
Solving several problems at once with different variables, unknowns, formulas and integrals.

The program opens up great opportunities, but you need to learn how to use it correctly.

How does Solver work?

In addition to the solver, Excel has a function called parameter selection. It is recommended for use in cases where there is only one unknown value. This program feature requires much less computer resources, so it will produce results faster.

Finding solutions in Excel 2007 is used for the most complex problems, where there are several unknowns and variables are often encountered. In general terms, they can be formulated as follows:

Find unknowns→several “x”.
Provided that→formula or function.
For restrictions → inequality or minimum/maximum values are usually indicated here.

You also need to indicate the cells with which calculations should be performed. It is possible to solve several different problems if you set the program the appropriate parameters.

Configuring Solutions Search options

In order for the Solution Finder function in Excel 2007 to work as desired by the user, you must enter the correct parameters. Usually they are limited to 1-3 characteristics, but with more complex tasks global settings will be required.

The options in the Office Excel 2007 Solution Finder may be as follows:

Maximum time - the number of seconds that the user allocates to the program to solve. It depends on the complexity of the task.
Maximum number of integrations. This is the number of moves the program makes on the way to solving the problem. If it increases, then no response will be received.
Error or accuracy is most often used when solving (for example, up to 0.0001).
Tolerance. Used when working with percentages.
Non-negative values. It is used when solving a function with two correct answers (for example, +/-X).
Show integration results. This setting is indicated if not only the result of decisions is important, but also their progress.
The search method is the choice of an optimization algorithm. Typically, Newton's method is used.

After all the settings have been selected, be sure to click the save button.

Problem parameters in the Search for solutions function

An add-in such as Finding a Solution in Excel works in accordance with the specified calculation characteristics. The most important of these is the method. There are two options. Newton's Method is the default setting. It is capable of handling more memory but less integration. Therefore, it is quite suitable for standard and not particularly complex equations.

There is also the “conjugate gradient method”. This requires less memory but requires more integrations. Consequently, when using it, you can solve the most complex equations and use large-scale formulas and functions.

Formula in Excel

There is a mandatory element without which the Solution Search add-in in Excel 2007 cannot function - formulas. They represent an expression that performs a particular calculation. Without equality, formulas do not exist. Therefore, the program will not begin to recognize it when the corresponding sign is missing.

The formula may include the following:

Function. This is a standard formula, where there is a certain and specific order of actions, which cannot be changed.
Link. It indicates the number of cells that need to be solved. In this case, the cells can be arranged randomly or in a certain order.
Operator. This is a symbol that specifies the type of calculation (+ - addition, * - multiplication, etc.).
Constant. A constant value that never changes. Also, to obtain it you do not need to make calculations.

Formulas are solved from left to right, subject to all mathematical rules.

Creating a Formula

Formulas are equations that help perform a program's calculations. If you do not enter these, then Search for a solution in Excel will not work. Problems, accordingly, will also not be solved. Therefore, to successfully complete the task, you must enter the formula correctly.

The calculation begins with an equal sign. For example, if the cell contains “=ROOT(cell number)”, then the corresponding function will be used.

After the main formula with the “=” sign has been printed, you need to indicate the data with which it will interact. This can be one or more cells. If the formula is suitable for 2-3 cells, then you can combine them using the “+” sign.

You can use the search function to find the information you need. For example, if you need a formula with the letter “A”, then you need to indicate it. Then the user will be offered all the data that includes it.

Conclusion

Finally, in Excel 2007 you need to save the specified parameters for solving problems. There are several ways to do this. The standard option with a click on the corresponding button is suitable if one calculation method is used for all data.

When you need to solve several equations at once, for example, to find the minimum and maximum of functions, then you need to save not the entire calculation, but its models. The user can then apply them to a particular solution.

The Solver Excel add-in is an analytical tool that allows us to quickly and easily determine when and what result we will get under certain conditions. The capabilities of the solution search tool are much higher than what “ parameter selection» in Excel.

The main differences between finding a solution and selecting a parameter:

Selecting several parameters in Excel.
Imposing conditions to limit changes in cells that contain variable values.
Possibility of use in cases where there may be many solutions to one problem.

Examples and problems to find solutions in Excel

Let's look at the analytical capabilities of the add-in. For example, you need to save $14,000 in 10 years. For 10 years, you want to put $1,000 into a bank deposit account every year at 5% per annum. The figure below contains a table in Excel, which clearly shows the balance of accumulated funds for each year. As can be seen, under such conditions of a deposit account and savings contributions, the goal will not be achieved even after 10 years. When solving this problem, you can go in two ways:

Find a bank that offers a higher interest rate on deposits.
Increase the amount of annual savings contributions to your bank account.

We can change the variable values in cells B1 and B2 so as to select the necessary conditions for accumulating the required amount of money.

The “Solution Search” add-on allows us to simultaneously use 2 of these options to quickly simulate the most optimal conditions for achieving the goal. For this:

As you can see, the program slightly increased the interest rate and the amount of annual contributions.

Limiting parameters when searching for solutions

Let's say you go to the bank with this table, but the bank refuses to raise your interest rate. In such cases, we need to find out how much we will have to increase the amount of annual investments. We must set a cell constraint with one variable value. But before you start, change the values in the variable cells to the original ones: in B1 by 5%, and in B2 by -$1000. Now let's do the following.