The IF function is one of the most used functions in Excel. This page contains many easy to follow IF examples. The IF function checks whether a condition is met, and returns one value if true and another value if false. Explanation: if the price is greater than , the IF function returns High, else it returns Low. You can use the IF function to display an empty string if the end value hasn't been entered yet see row 5. Explanation: if the end value is not empty means not equal to , the IF function calculates the progress between the start and end value, else it displays an empty string two double quotes with nothing in between.
The IF function in Excel can be nested, when you have multiple conditions to meet. For that, click on What if analysis and select Scenario manager. Then it will ask for the Scenario name and changing cells. Give scenario name whatever you want as per your requirement. Changing cells is the range of cells that your scenario values for different scenarios. Suppose if we observe the below screenshot. Whenever you click on the scenario and show the results at the back will change.
If we want to see all the scenarios at a time to compare with others, click on the summary the following screen will come. Here the columns in grey color are the changing values, and the column in white color is the current value which was the last selected scenario results. Suppose we will take a small example of product sale. Suppose we know that we want to sell the product at an additional price of than product cost then we want to know what is the percentage we are earning the profit.
Observe the above screenshot product cost is , and I have given the formula for finding the percentage profit, which you can observe in the formula bar. In one more cell, I gave the formula for the additional price which we want to sell.
In the set, the cell gives the cell position where we are going to give the output value here the additional price amount we know which we are giving in cell C4. The profit percentage is 30, and the selling price should be Similarly, we can check for different targeted values.
This goal seeks to help to find the EMI calculations etc. Now we will see the Data table. As a best practice, the original worksheet data should be saved as a scenario so that you can revert to it after all the experiments have been completed.
Click OK. As described in our scenario name and comments, we only adjust cell B3 by changing the value to To experiment with adjusting multiple variables within one scenario, the steps are the same as above, with the exception that the desired changes would be made in the Scenario Values window.
To see the outcome of each adjustment on the output cell s , either double click on a scenario name, or highlight a name and click Show. There are two report types available — Scenario summary and Scenario PivotTable report. Result cells are the cells that will be displayed in the summary. Ideally, these should include all cells which were adjusted as well as result cells. Note that if named ranges were created for Changing or Result Cells, range names will be displayed instead of cell references.
Selecting the Scenario PivotTable report type will create a pivot table report in a new worksheet. Learn more about pivot tables from our Resource Library. The third what if analysis tool from the Forecast command group is the Data Table.
Data tables allow the adjustment of only one or two variables within a dataset, but each variable can have an unlimited number of possible values. Data tables are designed for side-by-side comparisons in a way that makes them easier to read than scenarios, once they are set up correctly.
If the only variable to be considered in our loan example were the amount being borrowed, we could set up a one-variable data table. Step 1 - make a list of all possible principal loan amounts. The list may be by column or row. In our example, we will enter a column list in the range D9 to D Step 2 - In an adjacent column, enter the formula which was used to arrive at the original outcome. This links our new data table to the original variables. Step 3 - Select the entire data table range, including the list of variable values, the formula, and blank cells.
Step 5 - In the column input cell field since we entered our variables in column format , enter the cell reference that was used to calculate the result in the original dataset. In the above example, this would be cell B1 since this is the variable we have adjusted. Step 6 - Select OK. The result is a list of outcomes created by adjusting the one variable in cell B1, assuming that all other variables remain constant.
To create a row-oriented data table, the variables would be listed horizontally, and the row input cell would be used in the Data Table window instead of the column input cell. When creating a two-variable data table, one set of values is listed horizontally and the other set is listed vertically. In our example, we will add the loan period term as our second variable, displayed horizontally.
In this case, the formula which was used to arrive at the original outcome must be replicated above the vertical list of variables. As before, highlight the entire data table range and select Data Table from the What If Analysis menu.
The row input cell is the cell reference B3 that corresponds to the horizontal variables from the original dataset, while the column input cell B1 corresponds to the vertical variables. When we select OK, Excel returns a matrix that can be used to compare the outcome of different changes to our original scenario. It may be necessary to adjust the output cells to the appropriate number format for your data type in the case of the above example, currency.
Claudia is a content writer and course instructor at GoSkills. If she's not at work, she's probably tackling a genealogy project. Please login or sign up to comment. Learn about it here. Learn more here! Toggle navigation GoSkills. Search for courses or lessons Search.
See resources. See certification.
0コメント