Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Table Issues
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Table Issues
I was sure that the "row input" and "column input" cells needed to have
values, not formulas, in them. 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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Table Issues
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Table Issues
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Table Issues
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Table Issues
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. "DA" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 Pivot Table Issues | Excel Discussion (Misc queries) | |||
Pivot Table formatting issues | Setting up and Configuration of Excel | |||
pivot table formatting issues | Excel Discussion (Misc queries) | |||
Pivot table grouping issues | Excel Discussion (Misc queries) | |||
table issues | Excel Discussion (Misc queries) |