using a cell reference for data table input

[gotmike]

i feel like i have done this a hundred times, but for whatever reason its not working for me...

i have a data table that looks like this:

RATE	Monthly
	289.68
1.00%	262.81
2.00%	276.04
3.00%	289.68
4.00%	303.74
5.00%	318.20
6.00%	333.06

<tbody>
</tbody>

all the RATE numbers are manually entered.

now, if i reference the 3.00% number under the RATE column to the INPUT cell that I entered when I setup the data table, this happens:

RATE	Monthly
	289.68
1.00%	262.81
2.00%	276.04
3.00%	276.04
4.00%	303.74
5.00%	318.20
6.00%	333.06

<tbody>
</tbody>

this is obviously not correct, it is the value for the entry above.

and...

if i make ALL of the RATE cells then relative to the 3.00% one... (in other words 2.00% is actually =A5-0.01 and 4% is actually =A5+0.01)

i get...

RATE	Monthly
	289.68
1.00%	262.81
2.00%	250.00
3.00%	250.00
4.00%	262.81
5.00%	289.68
6.00%	333.06

<tbody>
</tbody>

this is weird b/c the number for the first one and last one never change, but all the ones in-between change.

incidentally, the formula which is being solved is simply a 30k loan pmt for 10yrs ( =PMT(RATE/12,120,30000)*-1 )

 

[RoryA]

What's the question?

(Do not try and link the cells to the input cell - it's effectively creating circular referencing)

 

[gotmike]

the question is that i want a single input to set both the primary case for the entire model as well as the input for the central case for my sensitivity analysis.

in other words, in the above situation, i want to run an analysis based on 3% and i also want to have a set of data tables that shows me values based on 3%.

so it sounds like you are telling me it's a circular reference, what's the workaround here? as i said at the outset, i feel like i've done this before without an issue.

do the inputs to a data table ALWAYS have to be static?

 

[RoryA]

Try putting the initial value in a separate cell, then link both other cells to that, so there is no direct link between them.

 

[gotmike]

That worked... It took a little playing with but that was the solution.

I'm still not clear why pointing directly to the input cell is a circular reference but point to a cell that in turn points to the input cell is not a circular reference.

Is there documentation somewhere that explains this in further detail?

 

[RoryA]

Not that I've ever seen, no. My assumption is that Excel effectively performs a substitution of the various input cells into the formula cell and gets confused because, if say the input cell is A3, it's putting =A3 into A3.