Sabrina Ayon
While working with Microsoft Excel, create a complicated formula that depends on a number of different inputs, and we wish to understand how altering these inputs and output affects the output. Build a What-If Analysis Data Table rather than evaluating per variable separately so we can quickly see all possible results. In this article, we will demonstrate two different examples to create a Two Variable Data Table with What If Analysis in Excel.
Download Practice Workbook
You can download the workbook and practice with them.
What Is Two Variable Data Table in Excel?
Data Table variables are one sort of constant that could function as a dataset or a straightforward worksheet with columns and rows and hold large amounts of data. A two-variable data table demonstrates how different variations of the quantities of the two sets of variables affect the formula outcome. In other terms, it demonstrates how altering two information values can alter the result of a given calculation.
2 Ideal Examples of Creating Two Variable Data Table with What If Analysis in Excel
Users can evaluate two variables or values concurrently or at once in a data table for computation by using the Excel What-If Analysis data table feature. Let’s look at the examples for two-variable data tables.
1. Create Two Variable Data Table for Monthly Payment Using Excel What If Analysis
In this example, we have a dataset containing loan amount, interest rate, number of payments, and monthly payments. We will calculate the monthly payments using the PMT function. It is a component of the financial functions and determines the loan amount based on fixed repayments and a fixed mortgage rate. Let’s see the change of two of the input variables simultaneously.
STEPS:
- Firstly, select the cell where you want to put the PMT function to compute monthly payments.
- Secondly, put the formula into that selected cell.
=PMT(C5/12,C6,C4)- Thirdly, press the Enter button on your keyboard to see the result.
- Now, we need to set up a two-variable data table, we take months (starting with months of 60 to 156) on the x-axis and loan amount (starting with $80,000 to $120,000) on the y-axis.
- Choose cell F3 and allocate the value of cell C7 which is monthly.
- Then, press Enter.
- At this point, select the whole data table.
- After that, go to the Data tab from the ribbon.
- Consequently, click on the What-If Analysis drop-down menu, under the Forecast group.
- Subsequently, select the Data Table from there.
- Thus, the Data Table dialog box will pop up.
- Select cell $C$4 for the Row input cell field and cell $C$6 for the Column input cell.
- Click on the OK button to complete the process.
- Hence, the data table will result automatically based on the data value.
- If you click any of the cells of the data table, you will find the formula down.
{=TABLE(C4,C6)}- The formula depends on the dataset that has all the information.
- We can not change the value of the data table. If you want to change the value, a Microsoft Excel pop-up window will appear which says Can’t change part of a data table.
Read More: How to Create One Variable Data Table Using What If Analysis
2. Utilize Excel What If Analysis to Generate Two Variable Data Table for Future Value
We will now generate two-variable data tables for future values. And we will get the future value using the FV function. The FV function determines the value of a particular investment using a fixed rate of return as a component of financial functions.
STEPS:
- In the first place, choose the cell where you want to insert the FV function to calculate future value.
- Next, enter the formula into the cell you just chose.
=FV(C5/12,C6*C7,-C4)
- To view the outcome, press the Enter key on your keyboard.
- Now that we have a two-variable data table set up, we will use the x-axis to represent the interest rate (ranging from 5% to 12%) and the y-axis to represent the number of years (ranging from 12 to 16).
- Select cell F3 and enter the future value from cell C8 there.
- After that, hit Enter.
- Pick all of the data in the table at this stage.
- After that, use the ribbon to navigate to the Data tab.
- Therefore, under the Forecast group, select the What-If Analysis drop-down menu.
- Select the Data Table from there after that.
- The Data Table dialog box will appear as a result.
- Choose cell $C$5 for the column input cell and cell $C$7 for the row input cell field.
- To end the procedure, click the OK button.
- Therefore, based on the input value, a data table will automatically be generated.
Read More: How to Perform What If Analysis in Excel (3 Examples)
Things to Keep in Mind
- The complete array range must be selected before pressing the Delete key on the keyword if a user wishes to alter or delete a portion of the output from the data table. The preceding approach must be used if the user chooses to utilize Ctrl + Z or undo, respectively.
- In situations where two variables alter at once, it is useful to consider the outcome.
- The related formula for the Data Table doesn’t need to be updated manually.
- Data tables differ from Excel tables, which are used to manage a collection of connected data.
Conclusion
The above examples will assist you to create Two Variable Data Table with What If Analysis in Excel. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback. Or you can glance at our other articles in the ExcelDemy.com blog!
Related Articles
- How to Delete What If Analysis in Excel (2 Simple Methods)
- What-If Analysis in Excel with Example
- How to Do What-If Analysis Using Scenario Manager in Excel
- Do What-If Analysis Using Goal Seek in Excel
- How to Use What If Analysis in Excel (with All 3 Features)
- What If Analysis Data Table Not Working (Issues with Solutions)
- How to Perform What-If Analysis in Excel (2 Ways)
