In this screencast, I'm going to talk about two-way data tables. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.Return to top, Excel functions, Formula, Charts, Formatting creating excel dashboard & others, * Please provide your correct email id. Creating a two-variable data table in Excel. Go to Data, then select What if Analysis and Data Table. However, you can manually delete all the values from the table. To create a two-variable data table, you enter two ranges of possible input values for a formula in the Data Table dialog box. Go to Data Tab –> Data Tools –> What if Analysis –> Data Table. Look at the cells that I have highlighted. 2) Two-Variable Data Table in Excel. Two-variable data table helps us to analyze how the combination of two different variables impact on the overall data table. We have used another example in this article to work with the two-variable data table. This has been a guide to creating Two-Variable Data Table Excel. Once you have calculated the values using data table, it can not be undone using Control + Z. In such a situation, a two variable data table should be used. In the first attempt, we need to wait for 25 years to get the sum of 65 lakh at a 10.5% return. Enter a range of values for the Row Input Cell across […] The word itself suggests two variables involved in this data table. Two Variable Data Table. Below are the basic details to do the sensitivity analysis. So now the objective profit per flight must be placed directly above the column input variable, fuel cost. You are not sure when to stop investing money and what is the percentage you are expecting. We can use a two-variable data table to analyze scenarios if two variables change at a time. Here are the steps to set up a Two variable data table in Excel: For example, if you want to identify the combinations of Loan Amount and Number of Monthly Payment that would result in a Monthly Payment of less than \$500 per month payout, you can simply use this 2 variable data table method. 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). If you want to analyze data when more than 2 variables change, scenario manager is the way to go. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. In the ROW, input cell select give the link to the cell B2 (that contains no., years). In a column, have all the different values that you want to test for Number of Monthly Payments. Now the data is all set to be used for a two variable data table calculation. Now give a link to the cell F4 from B5 (which contains the future value for our original investment). Assume that you are taking a loan from the bank and are in discussion with the bank manager regarding your interest rate and the repayment period. Now click on Data Table. If you can negotiate for this rate, you need to pay a monthly EMI of Rs. That means here it will affect both Row & Column. In this screencast, in a two-way data table or a two-way case study, we're going to investigate the simultaneous effect that two inputs have on an output. Create a new workbook. You can not delete/modify any one cell in the entire set of calculated values. You need to negotiate with the bank for an interest rate of 18.5% per anum for 3 years. When you create a two-input table, you specify input cells in the Row Input Cell and Column Input Cell boxes in the Table dialog box. 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).. To create a two variable data table, execute the following steps. Assume you are investing money in mutual funds through SIP planning. In cells B15:B19, type the following data: 3. Now click on, OK. In this example, a company wants to make a direct-mail promotion to sell its product. Apply FV function to know the future value after 25 years of investment. In this example, we are testing for 72, 84, 96… 240. You May Also Like the Following Excel Tutorials: I have a table which contains the employees age in range against the salary range and wanted to retrieve the number of employees with the age against the salary, Ages Salary 15-25 26-30 31-3 18-25 1 2 3 26-30 4 5 6 31-35 7 8 9 Require excel formula when the age is punch between the age and salary the corresponding value gets displayed.