Perform "What-if" analysis with a 2 input Data Table
Here are the steps to follow in this video lesson:
- Create a formula that references cells – e.g. =PMT(B4/12,B5,B3)
- To the right of this formula cell, enter a series of “substitute” values – e.g. 36, 48, 72 months.
- Directly below the formula cell, enter another series of “substitute” values – 4%, 4.5%, 5%, etc.
- Select the range of cells for the Data Table – starting with the formula cell.
- From the Excel Menu, choose Data, Table.
- For the “Row Input Cell,” click the cell in the original formula for which you want to “substitute” the row values in your Data Table.
- For the “Column Input Cell,” click the cell in the original formula for which you want to “substitute” the column values in your Data Table. Then click OK.
December 29th, 2008 at 5:32 PM
[...] Perform “What-if” analysis with a 2-input data table [...]
March 29th, 2009 at 3:51 PM
[...] are part of Excel’s “What-If” Analysis tool set – aka Data Analysis. Use Scenarios when you need to know what the result will be when you change [...]
December 16th, 2010 at 9:47 AM
[...] Perform “What-if” analysis with a 2-input data table [...]