The Double Negative
October 21, 2021
Working with Formulas in Excel
October 28, 2021
Understanding 2 variable Data Tables
This tool helps you present different results in a table by using different combinations of inputs that have been used in a formula. As for now, Microsoft Excel allows data tables by changing either one or two variables.
Here we will be explaining, how to create data tables by changing two variables in a formula.
In the above video, PMT is calculated using three variables interest, years (no. of periods) and present value (PV). We will be creating a data table by using different combinations of interest rates and years.
Step 1: After making a table with the combinations you would like to analyze, link the cell with a formula to the top left corner of the table created as shown below.
Step 2: Select the table and go to the Data Table option.
Data Tab > Forecast Section > What If Analysis > Data Table
Step 3: The dialog box that appears has two parts Row Input cell & Column Input cell.
Row Input cell: Select the cell that is used as an input in the formula and is populated in the first row of the table. Highlighted in blue. Column Input cell: Select the cell input that is used in the formula and is populated in the first column of the table. Highlighted in green.
Row Input cell:First row of the table shows no. of years and the cell used to represent the same in the formula is C7, shown in blue. Column Input cell:First column of the table shows interest and the cell used to represent the same in the formula is C6, shown in green.
The data table created above helps in knowing things like, ‘What if the interest increases to 11% and I take a loan for 4 years instead, how much EMI will I have to pay then?’
You need to pay $5169.1.
With data tables by your side, you can answer more such “what if” questions in lesser time.
Contributor: Sonia Keswani
I am a B.Com(Hons.) graduate from South City College (Morning) and have been working at Leveraged Growth as a Finance Intern for the past 1 month. I am pursuing CFA and aspire to be a Financial Analyst in the future.
