Now, we know how to perform What If Analysis with Data Table in Excel.
We can use a two-variable data table to illustrate how a different value of two variables in a certain formula changes the result of that formula. Data Table Not Working in Excel (7 Issues & Solutions).How to Create a Data Table in Excel (7 Ways).Example of Excel Data Table (6 Criteria).How to create a one variable data table in Excel.Enter the cell reference ( C5 ) for the input cell in the Column input cellįinally, we get the payment per period values for the corresponding values of the annual rate of interest.In this illustration, cell G4 contains the formula for calculating pmt-payment per period. Then, we put different values for the annual interest rate in a column seen in the screenshot and, the column right to that we’ll calculate different pmt values corresponding to these changing interest rates. This time our data table is column-oriented, we entered the formula to calculate pmt-payment per month in the first row of the data table. Enter the cell reference (C6) for the input cell in the Row input cellįinally, we get the payment per period values for the corresponding values of no of payment periods.ġ.2 One-Variable in the Column Input Cell.Click on the What If Analysis dropdown and choose Data Table.įollowing the above steps will open up a window:.Go to the Data tab in the Excel Ribbon.Select the data table along with the cell that contains the formula.In this illustration, cell I6 contains the formula for calculating pmt-payment per period. Then, we put different values for no of payment periods (nper) in a row seen in the screenshot and, the row below that we’ll calculate different pmt values corresponding to these changing nper values. 1.1 One-Variable in the Row Input CellĪs our data table is row-oriented, we entered the formula to calculate pmt-payment per month in the first column of the data table. One variable data table can be used when we want to see the results that change with different values of one input variable. Now, with this dataset, we are going to evaluate different outputs for one-variable change (interest rate and term separately) and also for t wo-variable (interest rate and term together) change. Result: Payment per Period ( pmt-monthly) = 830 Pv= 40,000 the present value is the total loan amount Rate = D5/12 D5 represents the annual interest rate of 9%, we divide it with 12 to adjust is for monthly. Let’s put the following formula in the cell D7Ĭompare it with =PMT(rate, nper, pv,, ) We used the PMT function to calculate the monthly payment to pay a loan of 40,000 dollars at an interest rate of 9% with 60 payment periods. Let’s introduce the dataset we’re gonna use in this article. But it can produce as many results as we want for these two variable combinations. Let’s get familiar with them:Ģ Ways to Perform the What If Analysis with Data TableĪ data table cannot analyze data for more than two variables (one for the row input cell and another for the column input cell). Note: There are three types of What If Analysis in Excel. For making decisions, it is useful if we could see the results of the formula based on changing values of these input variables.įor example, a data table can be helpful to decide on the monthly payment to repay a loan as it’ll provide us a range of monthly payments based on different interest rates and payment terms. In most cases, the outcome of a formula depends on multiple input variables. In Excel the What If Analysis is used to see how different values of an input variable of a formula affect the outcomes of the formula. Introduction to the What if Analysis with Data Table
0 kommentar(er)
