How to Create a One-Variable Data Table in Excel 2010
※ Download: Excel what if analysis data table
Example There is a loan of 5,000,000 for a tenure of 30 years. Other types of what-if analysis For more advanced projects, you may want to consider the other types of what-if analysis: scenarios and data tables. One-variable Data Tables A one-variable Data Table can be used if you want to see how different values of one variable in one or more formulas will change the results of those formulas.
Instead of consisting of two columns, there should be one column containing the values for the first variable, with a row containing the values for the second variable. The second field, To value:, is the desired result. If you adjust the rate, periods or amount, the formula result changes, so you can see the effect of these different variables. Sometimes a formula depends on multiple inputs, and you'd like to see how different inputs values would impact the result.
Data Analysis [Part 2 of 5] – Two Variable Data Table in Excel - Any formula in a one-variable data table must refer to only one input cell. By To create a two-variable data table to perform what-if analysis in Excel 2010, you enter two ranges of possible input values for the same formula: a range of values for the Row Input Cell in the Data Table dialog box across the first row of the table and a range of values for the Column Input Cell in the dialog box down the first column of the table.
Two-variable data table is best suited in situations when you want to see how the final result changes when two of the input variables change simultaneously as against where only one of the input variable changes. If you want to analyze data when more than 2 variables change, is the way to go. When to use Two Variable Data Table in Excel Suppose you have a data set as shown below: In the above data set, we have the Loan Amount, Interest Rate, and Number of Monthly payments. Based on these 3 input variables, Monthly Payment is calculated it is in red as it is an outflow of money. In such a situation, a two variable data table should be used. In this example, we are testing for 72, 84, 96… 240. At the same time, have the different loan amount values in the row just above the column values beginning one cell to the right as shown in the pic below. This is a construct that needs to be followed when you work with two variable data table. Also, make sure that the value in cell D1 is dependent on both the variables Number of Monthly Payments and Loan amount. In this case, cell D1 refers to cell B4, which has a value calculated using a formula which uses cells B1, B2 and B3. As soon as you click OK, it instantly fills the all the empty cells in the selected data range. It quickly gives you a view of Monthly Payments for a various combinations of Loan amount and Number of Monthly Payments. You can however manually select all the values and delete it. Since this is an array, you will have to delete all the values.
The first field, Set cell:, will contain the desired result. And we can add some basic formatting borders and currency to make things look slightly better. Each copy of this formula in the data table uses the growth rate percentage in the same row in column B to determine the possible outcome. Excel creates the data table in the range C8:C17 by entering a formula using the TABLE function into this range. In this way, you can view what-if results without changing the original formula's references. You currently have a grade of 65, and you need at least a 70 to pass the class.
