Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Data -> Table Problem
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding data table with different data to a graph | Charts and Charting in Excel | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Problem with external data | Excel Discussion (Misc queries) | |||
1-variable Data Table dependent on MS Query fails to update correctly | Excel Discussion (Misc queries) | |||
Colors of columns after sorting data in the supporting table | Charts and Charting in Excel |