What-if analysis in Excel

Excel contains many powerful tools for performing complex mathematical calculations, such as What if analysis. This tool is able to experimentally find a solution to your original data, even if the data is incomplete. In this tutorial you will learn how to use one of the tools “what if” analysis called Parameter selection.

Parameter selection

Every time you use a formula or function in Excel, you collect the original values ​​together to get the result. Parameter selection works the other way around. It allows, based on the final result, to calculate the initial value that will give such a result. Below we give some examples to show how it works. Parameter selection.

How to use Parameter Selection (example 1):

Imagine that you are going to a certain educational institution. At the moment, you have scored 65 points, and you need a minimum of 70 points to pass the selection. Fortunately, there is a final task that can increase your points. In this situation, you can use Parameter selectionto find out what score you need to get on the last assignment in order to get into an educational institution.

In the image below, you can see that your scores for the first two tasks (test and writing) are 58, 70, 72, and 60. Although we don’t know what your score for the last task (test 3) will be, we can write a formula that calculates the average score for all tasks at once. All we need is to calculate the arithmetic mean of all five ratings. To do this, enter the expression =CORE(B2:B6) to cell B7. After you apply Parameter selection To solve this problem, cell B6 will display the minimum score that you need to get in order to enter an educational institution.

1. Select the cell whose value you want to get. Every time you use the tool Parameter selection, You need to select a cell that already contains a formula or function. In our case, we will select cell B7 because it contains the formula =CORE(B2:B6).
2. On the Advanced tab Data select team What if analysis, and then from the drop-down menu, click Parameter selection.
3. A dialog box will appear with three fields:
   • Mouthupdate in cell is the cell that contains the desired result. In our case, this is cell B7 and we have already selected it.
   • Value is the desired result, i.e. the result that should be in cell B7. In our example, we’ll enter 70 because you need to score a minimum of 70 to get in.
   • Changing the value of a cell – the cell where Excel will display the result. In our case, we will select cell B6 because we want to know the grade we want to get on the last task.
4. After completing all the steps, click OK.
5. Excel will calculate the result and in the dialog box Parameter selection result provide a solution, if any. Click OK.
6. The result will appear in the specified cell. In our example Parameter selection set that you need to get a minimum of 90 points for the last task in order to move on.

How to use Parameter Selection (example 2):

Let’s imagine that you are planning an event and you want to invite as many guests as possible to stay within the $500 budget. You can use Parameter selectionto calculate the number of guests you can invite. In the following example, cell B4 contains the formula =B1+B2*B3, which sums up the total cost of renting a room and the cost of hosting all guests (the price for 1 guest is multiplied by their number).

1. Select the cell whose value you want to change. In our case, we will select cell B4.
2. On the Advanced tab Data select team What if analysis, and then from the drop-down menu, click Parameter selection.
3. A dialog box will appear with three fields:
   • Уput in a cell is the cell that contains the desired result. In our example, cell B4 is already selected.
   • Value is the desired result. We’ll enter 500 since it’s acceptable to spend $500.
   • Changesi cell value – the cell where Excel will display the result. We’ll highlight cell B3 because we need to calculate the number of guests we can invite without exceeding our $500 budget.
4. After completing all the steps, click OK.
5. Dialog window Parameter selection result will let you know if a solution has been found. Click OK.
6. The result will appear in the specified cell. In our case Parameter selection calculated the result 18,62. Since we are counting the number of guests, our final answer must be an integer. We can round the result up or down. Rounding up the number of guests, we will exceed the given budget, which means we will stop at 18 guests.

As you can see from the previous example, there are situations that require an integer as the result. If a Parameter selection returns a decimal value, round it up or down as appropriate.

Other Types of What-If Analysis

Other types can be used to solve more complex problems. “what if” analysis – Scenarios or data tables. Unlike Parameter selection, which builds on the desired result and works backwards, these tools allow you to analyze multiple values ​​and see how the result changes.

• Дscript manager allows you to substitute values ​​in several cells at once (up to 32). You can create multiple scripts and then compare them without manually changing the values. In the following example, we use scenarios to compare several different venues for an event.
• Tables data allow you to take one of the two variables in the formula and replace it with any number of values, and summarize the results in a table. This tool has the widest possibilities, since it displays many results at once, unlike Script Manager or Parameter selection. The following example shows 24 possible outcomes for monthly loan payments: