Currently Being ModeratedNov 1, 2012 4:40 AM (in response to Jerrold Green1)
I even tried the following
Make an array 20x20 for summary
Make 400 lines of data 1-400
In each summary cell put the following formula:
Where B collomn is the numbers in the data sheet and the index is changing in the summary matrix.
Than add some rows in the data sheet and see that summary is not adjesting.
I cannot send the problematic file, but I though maybe someone already saw this problem.
Currently Being ModeratedNov 1, 2012 5:43 AM (in response to Ziv Jacoby)
I've not seen that problem. Perhaps you could create a new file without sensitive information and if it displays the same problem you could send the dummy file.
Currently Being ModeratedNov 1, 2012 6:34 PM (in response to Ziv Jacoby)
First, I want to thank you for asking a question that got me thinking about something I hadn't really considered before.
I am not entirely sure I understand your question or the test you proposed in the second post, but if I am reading it correctly, what you are expecting is the way Numbers is designed to work (and the way I have seen it work) and when I try the test from your second post I get the results I expect.
That is, a cell reference points to a cell, not a location; if you move that cell (for example, by adding a row above it, which moves it down one row) the reference in the formula is updated to reflect the new location, but the result of the calculation doesn't change.
But this got me wondering about how Numbers dealt with references to ranges of cells. For example, the formula =SUM(C3:C6) is equivalent to =SUM(C3, C4, C5, C6), but what happens when you add a row after row 4? Apparently, the second expression remains a sum of the values in four cells, but the values of C5 and C6 are changed to reflect the fact that they have moved to C6 and C7. However, in the first expression, the new cell is added to the range, so that it is now the sum of five values. Actually, if you add a row (or column) to one that passes through a range, in the middle, or on the outside, the formula updates to add the new cell to the range, even if you are adding cells immediately below the bottom row in the range (so none of the cells in the original range actually move).
I'm not sure any of this has much to do with your problem, unless possibly one (or four) of your expressions is referring to a range that happens to be one cell, which would update differently from one referencing a simple cell. As Jerry has said, it's pretty hard to know anything without knowing what expressions your formulas are using.
At any rate, thanks for making me think about something I hadn't explored before.
Currently Being ModeratedNov 2, 2012 6:26 AM (in response to Ziv Jacoby)
I have two files that I already noticed this behaivor, but I could not repeat it using a simple array.
In my files I use many conditional rulings and many sheet document, maybe that is the reason Numbers gets the cells wrong.
Thank you all for now, I don't have a solution for my problem, but I'm positive there is one. If you hear something similar please let me know.