Data Table Issues

[DA]

In the past, if I wanted to use an EXCEL data table, I was sure that
the "row input" and "column input" cells needed to have values, not
formulas, in them. It needed to be, for example, 3, not even =3,
since the latter is, technically, a formula. That made sense to me
because you are
asking EXCEL to give you some other cell's result with different
values for this "input" cell. Recently, for unrelated reasons, I
replaced
such an input with a formula and, to my surprise, the data table
results did not change and the data table seems to work anyway, which
kind of
blows me away. You would think that EXCEL would object to changing a
cell that has a formula in it, wouldn't you? Generally, there are
many ways for a formula to get the same result (using other precedent
cells) so clearly EXCEL is not varying those precedent cells. It
seems that, for the purpose of the data table results, EXCEL is
simply
ignoring the formula, in essence, disconnecting it from its
precedents, and replacing it with the values you ask it to test.


I am using EXCEL 2003 SP3 in case that matters.


Are any of you seeing the same phenomenon? Does it make sense to
you? Is this phenomenon, perhaps, due to a recent update by Microsoft
to EXCEL 2003 that was not there
before? As I mentioned above, I almost surely recall times when the
data table would NOT work, because the input cell had something like
=
3*0.5, which I sometimes do, to remind myself how I got to the 1.5
'input' value. But now, it seems to have no problem with the "row
input" or "column input" being a formula.


Thanks
Dean

 

[Jim Rech]

I was sure that the "row input" and "column input" cells needed to have
I don't believe there was ever such a restriction. Excel is very good at
determining dependencies and calcing cells in the right order so I don't
think this would ever be a problem.

 

[DA]

Thanks for your reply, Jim. So, you see no problem with EXCEL
'breaking the link' between a cell and its precedent cells and simply
testing values in the former? To me, this is a dicey proposition. In
fact, that cell may never be able to have one of those values,
considering the cells that drive it.

Thanks,
Dean

 

[Jim Rech]

You seem to be interpreting what a data table does a bit differently than I
do. With a 2 way table you designate 2 cells outside of the table as input
cells. Those input cells drive some formula that references them. The top
left corner cell of your data table can be that cell or directly reference
it. The top row and first column of the data table (except for the top left
cell) provide values to be plugged into the 2 input cells behind the scenes
by Excel. From that point of view it makes no different if the values of
the top row and left column of the data table are as a result of directly
entering a constant, or as a result of a formula being calculated. All that
matters is the cell's value. If the value is '2' it can be because the cell
held '2' or =1+1 or =B12 where B12 is 2.

 

[DA]

I'll assume a one-dimensional data table to keep it simple

Suppose cell A1 really is an input and is equal to 3. Suppose cell A2
has a formula in it, that says =2*A1, so it is 2*3 = 6.

Suppose cell A3 has a formula in it that says = 2*A1 + 2*A2. So it is
equal to 2*3 + 2* 6 = 18.

I now create a data table for cell A3 with cell A2 as the input, i.e.,
varied parameter. So, if cell A2 takes on a value of 4, it blindly
will say that cell A3 is equal to 2*3 + 2*4 = 6+8 =4.

But, we know that for cell A2 to be equal to 4, cell A1 would need to
be equal to 2. But it clearly is still equal to 3.

Do you see my point?

Thanks

 

[Jim Rech]

Your example shows me that I misunderstood your point. I agree that doing
what you did in the example is a little odd and I would never have thought
to do it. On the other hand you could be interested to the values of A3
where the value of intermediate formula A2 is (some series) regardless of
how A2 got to those values. I mean, if A2 and A1 were really complex and
you didn't want to have to mess with them, just cut to the chase and imbue
A2 with these values. Pretty neat. Reminds me of the old Excel 4 macro
SET.VALUE function which could temporarily assign a value to a cell quite
apart from its actual value.