Performing a Sensitive Analysis Not calculating

Question

Attempting to run a sensitivity analysis for a real estate transaction. Using the Data and What if Data. to Receiving error for input cell reference is not valid. Prior to, the calculation was returning the same value for sensitivity analysis. Checked on cells and links.

Answer 1

Hi, Dennis D. Edwards Sr

Since the input cells and calculation settings have already been checked, here are a few other things to look at.

First, check that the row and column input cells are two different cells, and that neither one sits inside the range you select for the data table. Both of these give the same "Input cell reference is not valid" error.

Next, look at the values across the top row and down the first column of your table. If any of them are formulas linked back to the model (for example =B5+1%), Excel can return odd or repeated results, so it's recommended to type them in as plain numbers.

Real estate models often have a circular reference too, like interest feeding back into the loan amount. That can make every result in the table come out the same. Go to Formulas > Error Checking > Circular References to see if anything is listed. If something shows up, try breaking that loop (or hard-coding the value for now) and rerun the table.

Lastly, instead of linking the inputs to a new sheet, you can build the data table directly on the sheet where your assumptions already live. That way it uses the real input cells.

Answer 2

Place the Data Table’s row and column input cells on the same worksheet as the sensitivity table; selecting an input cell on another sheet commonly produces “Input cell reference is not valid.” ^1^

Correct the input-cell error

If the transaction model’s assumptions are currently on another worksheet:

1. On the worksheet containing the sensitivity table, create one local input cell for each variable—for example:
   • B3: interest rate
   • B4: exit cap rate
2. Change the model so its calculations refer to these local input cells. The model’s formulas and output may remain on other worksheets.
3. In the sensitivity table’s upper-left corner, enter a normal formula referencing the model output, for example:

      ='Returns Model'!$H$25
   

4. Select the entire sensitivity range, including:
   • The output formula in the upper-left corner
   • The row and/or column assumption values
   • The blank result area
5. Select Data > What-If Analysis > Data Table.
6. Enter direct, single-cell references from the current worksheet:
   • Row input cell: the model input represented by values across the top row
   • Column input cell: the model input represented by values down the first column
7. Select OK.

Do not enter a range, multiple cells, or a reference such as 'Assumptions'!B3 in either input box.

For a two-variable table, the upper-left formula must ultimately depend on two different input cells. Excel substitutes each row-and-column combination into those cells and records the formula result. ^2^

If every result is still identical

Since the cell links have already been checked, test these two specific conditions:

1. Verify each input independently
   • Manually change the local row input cell and confirm the output formula changes.
   • Restore it, then manually change the local column input cell and confirm the output changes.
   • If either change has no effect, that variable is not in the output formula’s dependency path. Correct the model reference before rebuilding the Data Table.
2. Enable Data Table recalculation
   • Go to File > Options > Formulas.
   • Under Calculation options, select Automatic.
   • Press Ctrl+Alt+F9 to force a full workbook recalculation.

If calculation is set to Automatic Except for Data Tables, ordinary formulas recalculate while sensitivity tables may remain unchanged until manually recalculated. Pressing F9 recalculates pending Data Tables; Ctrl+Alt+F9 forces calculation across all open workbooks. ^3^

Also confirm that the row and column variables have not been reversed: values across the top correspond to Row input cell, while values running downward correspond to Column input cell.

---

References

1. Excel 2007/ In put Cell reference is not valid While using What If Analysis
2. Calculate multiple results by using a data table
3. Excel Recalculation