Excel function: parameter selection

Excel pleases its users with many useful tools and functions. One of these is undoubtedly Parameter selection. This tool allows you to find the initial value based on the final value that you plan to receive. Let’s see how to work with this function in Excel.

Why is the function needed

As mentioned above, the task of the function Parameter selection consists in finding an initial value from which a given final result can be obtained. In general, this function is similar to Search Solutions (you can read it in detail in our article -), however, it is simpler.

You can use the function only in single formulas, and if you need to perform calculations in other cells, you will have to perform all the actions in them again. Also, the functionality is limited by the amount of data being processed – only one initial and final values.

Using the function

Let’s move on to a practical example that will give you the best understanding of how the function works.

So, we have a table with a list of sporting goods. We only know the discount amount (560 rub. for first position) and its size, which is the same for all items. You have to find out the full cost of the goods. At the same time, it is important that in the cell, which will later reflect the amount of the discount, the formula for its calculation was written (in our case, multiplying the total amount by the size of the discount).

So, the algorithm of actions is as follows:

1. Go to the tab “Data”in which we click on the button “what if” analysis in the tool group “Forecast”… In the drop-down list, select “Parameter selection” (in earlier versions, the button may be in the group “Working with data”).
2. A window will appear on the screen for selecting the parameter that needs to be filled in:
   • in field value “Set in cell” we write the address with the final data that we know, i.e. this is the cell with the discount amount. Instead of manually entering coordinates, you can simply click on the desired cell in the table itself. In this case, the cursor should be in the corresponding field for entering information.
   • As a value, we indicate the amount of the discount, which we know – 560 rub.
   • In the “Changing the value of a cell” manually or by clicking with the mouse, specify the coordinates of the cell (should participate in the formula for calculating the discount amount), in which we plan to display the initial value.
   • press when ready OK.
3. The program will perform the calculations and display the result in a small window that can be closed by clicking the button. OK. Also, the found values ​​will automatically appear in the specified cells of the table.
4. Similarly, we can calculate the undiscounted price for other products if we know the exact amount of the discount for each of them.

Solving Equations Using Parameter Selection

Despite the fact that this is not the main direction of using the function, in some cases, when it comes to one unknown, it can help in solving equations.

For example, we need to solve the equation: 7x+17x-9x=75.

1. We write an expression in a free cell, replacing the symbol x to the address of the cell whose value you want to find. As a result, the formula looks like this: =7*D2+17*D2-9*D2.
2. Clicking Enter and get the result as a number 0, which is quite logical, since we only have to calculate the value of the cell D2, which is the “x” in our equation.
3. As described in the first section of the article, in the tab “Data” push the button “what if” analysis and choose “Parameter selection”.
4. In the window that appears, fill in the parameters:
   • In field value “Set in cell” indicate the coordinates of the cell in which we wrote the equation (i.e. B4).
   • In the value, according to the equation, we write the number 75.
   • In the “Changing Cell Values” specify the coordinates of the cell whose value you want to find. In our case, this is D2.
   • When everything is ready, click OK.
5. As in the example discussed above, calculations will be made and the result will be obtained, as indicated by a small window.
6. Thus, we managed to solve the equation and find the value x, which turned out to be 5.

Conclusion

Fitting is a function that can help you find an unknown number in a table, or even solve an equation with one unknown. The main thing is to master the skills of using this tool, and then it will become an indispensable assistant during the performance of various tasks.