We have blogged before on data tables. Their power and efficiency for sensitivity analysis is impressive. But setting them up 100% correctly is critical, because if you do not, they will still run and output values, but the output values will be faulty.
The most dangerous mistake you can make when setting up data tables is to link one or more of the column and/or row variables to the variable cells in the model itself.
In other words, the top table is set up correctly, where NONE of the Rent PSF variables are linked to the model above. Cell e17 is an input, and the other rent values are computed dependents of that input.
The bottom table purposefully creates an error situation by linking e24 to c5.
Lesson: do NOT link any of your row or column variable headers to the model itself.
This is a perfect example of how Excel, in following your commands to the letter, can betray you.
Valuate does not allow this type of user error.
or
Thanks for the post Bruce, this was an issue I had in the past and could not trace back the error. I just hard keyed the increments manually but this certainly helps.
My pleasure. Excel should prevent this, but it does not, so we need to be extra careful.