Data -> Table Problem

[nilizandr]

Hi,
I'm getting incorrect values from the data table function found under the Data menu, the function is listed as "table."
I set up a simple example of this problem to demonstrate the bug in Excel. (This is a simple example, -I know this objective could be achieved without data table- but the project that I actually need to do is MUCH more complicated. The example is just to demonstrate the potential bug in Excel.)
In the diagram below, imagine the blank cell is A1 and the value 1 is in B1.
The simple request says that cells in column B = column A +1. Cells in column A = column B values from the row above *2. So, in A1 we have 0. B1 is A1+1, which =1. A2= B1*2, which equals 2. B2= A2+1, which equals 3. A3= B2*2, which equals 6.... But then the table function fails! The value in cell B3 goes back to 1 for some reason.

1
2 3
6 1
2 1
2 1
2 1
2 1
2 1
2 1


If I drag down the formula cell A2 (=B1*2) to refresh all of column A, one more row in the table gets updated, but the errors begin one row down in row 4 now. Take a look:

1
2 3
6 7
14 3
6 3
6 3
6 3
6 3
6 3


Why is the table() function generating these errors. What can I do to avoid them?
Thanks!!
Nili

[CyberTaz]

Hello nilizandr-

No offense, but as I understand the feature you're trying to force it to do
something it isn't designed to do. In effect, creating the equivalent of a
circular reference. I've used this feature for both 1- and 2-Input tables for
years without any "bugs".

If I interpret your post correctly, what you want can be done much more
simply if you try it this way:

1) Leave A1 blank and in B1 use your =A1+1, then use the Fill handle to
copy down as many rows as necessary,
2) In cell A2 enter =B1*2, then use the Fill handle to copy down to the
same row.

The result will be:

A B
1 1
2 2 3
3 6 7
4 14 15
5 30 31
6 62 63
7 126 127
8 254 255
9 510 511
10 1022 1023
11 2046 2047
12 4094 4095
13 8190 8191

HTH |:>)

"nilizandr" wrote:

>
> Hi,
> I'm getting incorrect values from the data table function found under
> the Data menu, the function is listed as "table."
> I set up a simple example of this problem to demonstrate the bug in
> Excel. (This is a simple example, -I know this objective could be
> achieved without data table- but the project that I actually need to do
> is MUCH more complicated. The example is just to demonstrate the
> potential bug in Excel.)
> In the diagram below, imagine the blank cell is A1 and the value 1 is
> in B1.
> The simple request says that cells in column B = column A +1. Cells in
> column A = column B values from the row above *2. So, in A1 we have 0.
> B1 is A1+1, which =1. A2= B1*2, which equals 2. B2= A2+1, which equals
> 3. A3= B2*2, which equals 6.... But then the table function fails!
> The value in cell B3 goes back to 1 for some reason.
>
> 1
> 2 3
> 6 1
> 2 1
> 2 1
> 2 1
> 2 1
> 2 1
> 2 1
>
>
> If I drag down the formula cell A2 (=B1*2) to refresh all of column
> A, one more row in the table gets updated, but the errors begin one row
> down in row 4 now. Take a look:
>
> 1
> 2 3
> 6 7
> 14 3
> 6 3
> 6 3
> 6 3
> 6 3
> 6 3
>
>
> Why is the table() function generating these errors. What can I do to
> avoid them?
> Thanks!!
> Nili
>
>
> --
> nilizandr
> ------------------------------------------------------------------------
> nilizandr's Profile: http://www.excelforum.com/member.php...fo&userid=8309
> View this thread: http://www.excelforum.com/showthread...hreadid=386433
>
>