Solve function in Excel. Enable, use case with screenshots

“Search for a solution” is an Excel add-in, through which it is possible to choose the best solution to problems based on the specified restrictions. The function makes it possible to schedule employees, distribute costs or investments. Knowing how this feature works will save you time and effort.

What is Search for Solutions

In combination with various other options in Excel, there is one less popular, but extremely necessary function “Search for a solution”. Despite the fact that finding it is not easy, getting to know it and using it helps in resolving many problems. The option processes the data and gives the optimal solution from the allowed ones. The article describes how the Search for a Solution directly functions.

How to turn on the “Search for a solution” feature

Despite the effectiveness, the option in question is not in a prominent place on the toolbar or context menu. Most users who work in Excel are not aware of its presence. By default, this function is disabled, to display it, perform the following actions:

1. Open the “File” by clicking on the appropriate name.
2. Click on the “Settings” section.
3. Then select the “Add-ons” subsection. All add-ons of the program will be displayed here, the inscription “Management” will appear below. On the right side of it there will be a pop-up menu where you should select “Excel Add-ins”. Then click “Go”.
   

   

4. An additional window “Add-ins” will be displayed on the monitor. Check the box next to the desired function and click OK.
5. The desired function will appear on the ribbon to the right of the “Data” section.

About Models

This information will be extremely useful for those who are just getting acquainted with the concept of “optimization model”. Before using the “Search for a solution”, it is recommended to study the materials on the methods of building models:

• the option under consideration will make it possible to identify the best method in order to allocate funds for investments, loading the premises, supplying goods or other actions where it is necessary to find the best solution.
• The “optimal method” in such a situation would mean: increasing income, reducing costs, improving quality, etc.

Typical optimization tasks:

• Determination of a production plan, during which the profit from the sale of released goods will be maximum.
• Determination of transportation maps, during which transportation costs are minimized.
• Search for the distribution of several machines for various types of work, so that production costs are reduced.
• Determination of the shortest time for completion of work.

Important! To formalize the task, it is necessary to create a model that reflects the main parameters of the subject area. In Excel, a model is a set of formulas that use variables. The considered option looks for such indicators that the objective function is greater (less) or equal to the specified value.

Preparatory stage

Before placing a function on the ribbon, you need to understand how the option works. For example, there is information on the sale of goods indicated in the table. The task is to assign a discount for each item, which would be 4.5 million rubles. The parameter is displayed inside a cell called target. Based on it, other parameters are calculated.

Our task will be to calculate the discount by which the sums for the sale of various products are multiplied. These 2 elements are connected by a formula written like this: =D13*$G$2. Where in D13 the total quantity for the implementation is written, and $G$2 is the address of the desired element.

Using the function and setting it up

When the formula is ready, you need to use the function itself directly:

1. You need to switch to the “Data” section and click “Search for a solution”.

2. The “Options” will open, where the required settings are set. In the line “Optimize the objective function:” you should specify the cell where the sum of the discounts is displayed. It is possible to prescribe the coordinates yourself or choose from the document.

3. Next, you need to go to the settings of other parameters. In the “To:” section, it is possible to set the maximum and minimum limits or an exact number.

4. Then the field “Changing the values ​​of variables:” is filled. Here the data of the desired cell is entered, which contains a specific value. Coordinates are registered independently or the corresponding cell in the document is clicked.

5. Then the tab “According to restrictions:” is edited, where the restrictions on the applied data are set. For example, decimal fractions or negative numbers are excluded.

6. After that, a window opens that allows you to add restrictions in the calculations. The initial line contains the coordinates of a cell or an entire range. Following the conditions of the task, the data of the desired cell is indicated, where the discount indicator is displayed. Then the comparison sign is determined. It is set to “greater than or equal to” so that the final value is not with a minus sign. The “Limit” set in line 3 is 0 in this situation. It is also possible to set a limit with “Add”. The next steps are the same.

7. When the above steps are completed, the set limit appears in the largest line. The list can be large and will depend on the complexity of the calculations, however, in a particular situation, 1 condition is sufficient.

8. In addition, it is possible to select other advanced settings. At the bottom right side there is an option “Options” that allows you to do this.

9. In the settings, you can set the “Limitation accuracy” and “Solution limits”. In our situation, there is no need to use these options.

10. When the settings are completed, the function itself starts – click “Find a solution”.

11. After the program performs the required calculations and issues the final calculations in the required cells. Then a window with the results opens, where the results are saved / canceled, or the search parameters are configured according to a new one. When the data meets the requirements, the found solution is saved. If you check the “Return to the solution search options dialog box” box in advance, a window with function settings will open.

12. There is a possibility that the calculations turned out to be erroneous or there is a need to change the initial data in order to obtain other indicators. In such a situation, you need to reopen the settings window and double-check the information.
13. When the data is accurate, an alternative method can be used. For these purposes, you need to click on the current option and select the most suitable method from the list that appears:

• Finding a Solution Using a Generalized Gradient for Nonlinear Problems. By default, this option is used, but it is possible to use others.
• Finding solutions for linear problems based on the simplex method.
• Using evolutionary search to complete a task.

Attention! When the above options failed to cope with the task, you should check the data in the settings again, as this is often the main mistake in such tasks.

14. When the desired discount is received, it remains to apply it to calculate the amount of discounts for each item. For this purpose, the initial element of the column “Discount Amount” is highlighted, the formula is written «=D2*$G$2» and press “Enter”. Dollar signs are put down so that when the formula is stretched to adjacent lines, G2 does not change.

15. The discount amount for the initial item will now be obtained. Then you should move the cursor over the corner of the cell, when it becomes a “plus”, the LMB is pressed and the formula is stretched to the required lines.
16. After that, the table will be finally ready.

Load/Save Search Options

This option is useful when applying various constraint options.

1. In the Solution Finder Options menu, click Load/Save.
2. Enter the range for the model area and click Save or Load.

When saving the model, a reference is entered to 1 cell of an empty column where the optimization model will be placed. During model loading, a reference is entered to the entire range that contains the optimization model.

Important! To save the last settings in the Solution Options menu, a workbook is saved. Each sheet in it has its own Solver add-in options. In addition, it is possible to set more than 1 task for a sheet by clicking the “Load or Save” button in order to save individual tasks.

A simple example of using Solver

It is necessary to load the container with containers so that its mass is maximum. The tank has a volume of 32 cubic meters. m. A filled box has a weight of 20 kg, its volume is 0,15 cubic meters. m. Box – 80 kg and 0,5 cu. m. It is required that the total number of containers is at least 110 pcs. The data is organized like this:

The model variables are marked in green. The objective function is highlighted in red. Restrictions: by the smallest number of containers (greater than or equal to 110) and by weight (=SUMPRODUCT(B8:C8,B6:C6) – total tare weight in the container.

By analogy, we consider the total volume: =SUMPRODUCT(B7:C7,B8:C8). Such a formula is necessary to set a limit on the total volume of containers. Then, through the “Search for a solution”, links are entered to elements with variables, formulas and the indicators themselves (or links to specific cells). Of course, the number of containers is an integer (it is also a limitation). We press “Find a solution”, as a result of which we find such a number of containers when the total mass is maximum and all restrictions are taken into account.

Search for solution failed to find solutions

Such a notification pops up when the function in question has not found combinations of variable scores that satisfy each constraint. When using the Simplex method, it is quite possible that there is no solution.

When a method for solving nonlinear problems is used, in all cases starting from the initial indicators of the variables, this indicates that the possible solution is far from such parameters. If you run the function with other initial indicators of the variables, then there is probably a solution.

For example, when using the non-linear method, the elements of the table with variables were not filled, and the function did not find solutions. This does not mean that there is no solution. Now, taking into account the results of a certain assessment, other data is entered into the elements with variables that are close to those received.

In any situation, you should initially examine the model for the absence of a constraint conflict. Often, this is interconnected with improper selection of the ratio or the limiting indicator.

In the above example, the maximum volume indicator is 16 cubic meters. m instead of 32, because such a restriction contradicts the indicators for the minimum number of seats, since it will correspond to the number of 16,5 cubic meters. m.

Conclusion

Based on this, the “Search for a solution” option in Excel will help in solving specific problems that are rather difficult or impossible to solve in the usual ways. The difficulty in applying this method is that initially this option is hidden, which is why most users are not aware of its presence. In addition, the function is quite difficult to learn and use, but with proper research, it will bring great benefits and facilitate calculations.