Go to Data Tab –> Data Tools –> What if Analysis –> Data Table Now the data is all set to be used for a two variable data table calculation.In this case, cell D1 refers to cell B4, which has a value calculated using a formula which uses cells B1, B2 and B3. It won’t work if you manually enter the value in cell D1. Also, make sure that the value in cell D1 is dependent on both the variables (Number of Monthly Payments and Loan amount). This is a construct that needs to be followed when you work with two variable data table. Type =B4 in cell D1, which is one row above the values in the column.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. In this example, we are testing for 72, 84, 96… 240.

In a column, have all the different values that you want to test for Number of Monthly Payments.Here are the steps to set up a Two variable data table in Excel: Setting up Two Variable Data Table in Excel In such a situation, a two variable data table should be used. For example, you may want to keep the Monthly Payment at $500 or less, and analyze what Loan Amount and Tenure combination can give you this. Now you may want to do an analysis to see what should be the ideal combination of Loan Amount and Number of Monthly Payment to suit your requirement. The following formula is used to calculate the Monthly Payment: =PMT(B2/12,B3,B1) Based on these 3 input variables, Monthly Payment is calculated (it is in red as it is an outflow of money). In the above data set, we have the Loan Amount, Interest Rate, and Number of Monthly payments. Suppose you have a data set as shown below: When to use Two Variable Data Table in Excel If you want to analyze data when more than 2 variables change, scenario manager is the way to go. 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 One Variable Data Table where only one of the input variable changes). Watch Video – Two-variable Data Table in Excel

How to do data analysis in excel on mac