What If Analysis and Excel Two Input Data Table

What-IF-Analysis is an important part of Excel. You can create What If Analysis and Excel Two Input Data Table to modify 2 variables value.

What If Analysis and Excel Two Input Data Table permits you to change two variables value in your sheet and view how modifying the values for that variables affect the values on calculated by one or more formulas in active sheet.

What If Analysis and Excel Two Input Data Table

What if analysis and excl two input data table allows you change the more than one variable values as per you need and get required result.

What-IF Analysis:

It is a method of modify the cell values and see how these modifications are taking affect the result of formulas on the worksheet. For example, varying the interest rate and term that is used in an amortization table to determine the amount of the payments.

Data Table:

Data table is an important part of the What-If-Analysis facility which can be used to change one or more than one variables and watch the result.

Must Read: Conditional Formatting – How to generate chess board on excel

Formula: A series of numbers, reference of any cell, use of functions or any operators which is used in a cell that provide the final resulted value. In MS Excel, a formula always starts with an equal sign (=) operator. You can create two-input data tables from what if analysis facility in MS Excel, depending on the number of variables that you want to test. For Example: If you take loan from any bank on different interest rate and for different periods in months and you want to know how changing interest rate percentage variable affect the installment amount in active sheet, then you can use one input data table facility in MS Excel .

How to create 2-Input Data Table

Step 1: If you want to create two input data table then first, you have to prepare following given sheet:

create-two-input-data-table

Step 2: Type following given formula in both
Cells D2 & A5: =PMT(A2/12,C2,-B2)   then press enter key to view the result. Excel shows $ 8908.29 as a result.

'What-If-Analysis'-Excel's-Two-Input-Data-Table

Step 3: Select the range of cells from A5:F11, after that you have to click on the “Data” Tab. Now, click on the “What-If Analysis” option.  Click on the “Data Table” option and  type $C$2 in the Row Input Cell dialogue box & type $A$2 in Column Input Cell  box in Data Table dialog box. Now, click on the ok button.

'What-If-Analysis'-Excel's-Two-Input-Data-Table

Step 4: Now, you will see the final result in two input data table:

'What-If-Analysis'-Excel's-Two-Input-Data-Table

Must Read: MS Excel : Vlookup Function with a Spreadsheet

I hope you like this What If Analysis and Excel Two Input Data Table article. If you have any important suggestion regarding this article the write us in the comment box. Share this article with your friends to show your knowledge in excel. Thanks.

One Comment

  1. My name is Ashenafi Birhanu Mesfin I work south will zone Kalu woreda Finance and economic development office I like all the income tax formula …

Leave a Reply

Thanks to leave a comment. Please note that all comments are moderated according to our comment policy and your email address will NOT be published. Please Do NOT use keywords in the name field. Lets have a meaningful conversation.