Trouble with data table 1

[Clyde38]

I'm not understanding the results from a data table that I've created (see attached). Table 1 has the incorrect values starting from 477.4648(3500) down. If I change the formula for the column data point "3000" to the constant 3000 the results are correct (Table 2). What is even stranger is Table 3. The results are correct and the same as Table 2, but the formula used to generate the column data doesn't make sense to me. In all three tables the formula for the data table is H9 (upper right for all three tables). I would appreciate any advice or insight for this issue.

 

[Clyde38]

I would just like to clarify my statement "the formula used to generate the column data doesn't make sense to me". I used this approach because I thought that the value in the table under "column data" would be incremented during the generation of the table. I guess I didn't expect this column to have the results that it has after the table was constructed (all the same value).

 

[IRstuff]

I'll assume that you had a more complicated equation than what's shown, since it would have obviously been easier to just copy that equation into the column.

Your results problem appear to have something to do with using H8 as the starting value in the data table. Since the TABLE function has to use H8 to do the array calculation, there's obviously some interaction; what it is, I don't know.

Nevertheless, if you simply make your first value row value 2500 instead of H8, the array works correctly.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529

 

[Clyde38]

Thanks IR,
Yes, the spreadsheet was just to show what's happening. It certainly has to do with the reference to F8 as the start for column data. See attached.

 

[IDS]

In Table 1 in your first spreadsheet you in effect have a circular reference because the column 1 values all link back to cell H8, which is incremented by the Data Table process, so in effect you add 1000 at each step, rather than 500.

Table 2, Column 1 doesn't link to cell H8, other than the first row, so the values remain unchanged.

Table 3, Column 1 does have the circular reference, but because the formulas all link through Cell H8, rather than the cell above, it results in the value being incremented by 500 at each step, even though they all display as 3000.

In your second spreadsheet the Column 1 values in Tables 1 and 2 all link to Cell F8, and the "Column input cell" is H8, so the circular reference is removed, and the tables work as intended. Table 3 still works the same as before.

I don't know why Excel doesn't pick up this circular reference, but it is certainly not obvious, and something to watch out for.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IDS]

From a quick search, the only reference I found to the same problem was:

http://answers.microsoft.com/en-us/...ow-is-it/5879edd9-edcf-4acd-9b6d-d62945a57aa9

which suggests that it is intended behaviour.

If so, it strikes me as being a very bad idea.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IRstuff]

That's fascinating, thanks..

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529

 

[Clyde38]

Thanks to you both for your input!