List of values that Excel substitutes in the column input cell, B3.ĭata tables are recalculated when a sheet is recalculated, even if they have not changed. List of values that Excel substitutes in the row input cell, B4. For example, you can use a two-variable data table to see how different combinations of interest rates and loan terms will affect a monthly mortgage payment.Ĭell C2 contains the payment formula, =PMT(B3/12,B4,-B5), which uses two input cells, B3 and B4. Use a two-variable data table to see how different values of two variables in one formula will change the results of that formula. List of values that Excel substitutes in the input cell, B3. You enter the variable values in one column or row, and the outcomes are displayed in an adjacent column or row.Ĭell D2 contains the payment formula, =PMT(B3/12,B4,-B5), which refers to the input cell B3. For example, you can use a one-variable data table to see how different interest rates affect a monthly mortgage payment by using the PMT function. Use a one-variable data table if you want to see how different values of one variable in one or more formulas will change the results of those formulas. You can create one-variable or two-variable data tables, depending on the number of variables and formulas that you want to test.
A scenario can have a maximum of 32 different values, but you can create as many scenarios as you want. Although it is limited to only one or two variables (one for the row input cell and one for the column input cell), a data table can include many different variable values. If you want to analyze more than two variables, you should instead use scenarios. Because you focus on only one or two variables, results are easy to read and share in tabular form.Ī data table cannot accommodate more than two variables. Using data tables makes it easy to examine a range of possibilities at a glance. Unlike scenarios, data tables show you all the outcomes in one table on one sheet.
Like scenarios, data tables help you explore a set of possible outcomes. Goal Seek works differently from scenarios and data tables in that it takes a result and determines possible input values that produce that result. Scenarios and data tables take sets of input values and determine possible results. There are three kinds of what-if analysis tools in Excel: scenarios, data tables, and Goal Seek. For example, you can use a data table to vary the interest rate and term length that are used in a loan to determine possible monthly payment amounts. What-if analysis changes the values in cells to see how those changes will affect the outcome of formulas on the sheet. When you use data tables, you are doing what-if analysis.
Data tables are part of a suite of commands that are called What-if Analysis tools.
0 kommentar(er)
