gizzzzmo has found an unexpected recalculation bug in Excel 2010.
You can duplicate it using the following steps.
Start Excel 2010 with a new blank workbook in Automatic Calculation mode.
- in A1 enter =MAX(A4:A5)
- in A2 enter =A1
- in A3:A5 create a Table (Insert tab->Table, check My Table has Headers)
- in A4 enter 1
- in A5 enter 2
It should now look like this:
Now enter 3 into cell A6 and the screen changes to this:
The Table has been extended to include cell A6 and the formula in cell A1 has automagically been changed to reflect this ( =MAX(A4:A6) ) , so cell A1 correctly shows 3.
But cell A2 still shows 2, even though its formula references cell A1, which has changed, and we are in automatic calculation mode so it should show 3. Recalc BUG!
If you now change cell A6 to 4 BOTH cells A1 and A2 correctly get recalculated, so the dependency tree is still valid.
This bug does not exists in Excel 2007, but is still there in my installation of Excel 2013 Tech Preview.
If you create the Table before entering the =MAX() formula then Excel creates a structured Table reference =MAX(Table1[Column1]) and recalculation works correctly.
Excel and UDF Performance Stuff 
2013 has gone RTM, so there is no chance of this getting fixed in a hurry, I guess we will have to wait for SP1
For 2010 we have to wait for SP2 as this bug not as reputation damaging as the Calculation Bug discovered in RTM of 2007
Yes, apparently the bug is still there in the latest 2013 builds (which I don’t have) so it will be in 2013 RTM. I suspect it will not get a high fix priority as the argument will be that its been there in 2010 for some time without being discovered.
But to me its a bit of an ugly one, and its really surprising that the dependency tree is correct BUT recalc fails because cell A1 has not been dirtied even though its formula has been changed and re-evaluated.
Only have XL07, but why not use =MAX(Table1) instead of =MAX(A4:A5). A4:A5 shouldn’t change just because it happens to be part of a list object… but Table1 ought to because it is a list object. In fact, I’d think that having the reference to A4:A5 change when you didn’t intend it to do so is the real bug.
When its a List or Table Excel treats appending a row the same way as inserting a row in the middle of an ordinary range (formulas get modified). I think this is useful behaviour. But yes the simplest bypass to the bug is to use structured references or the Defined Names of the Table/List instead of just vanilla references.
Hi – this is gizzzzmo –
Yes using table reference syntax is a convenient and easy method. Another is to append a volatile function to the formula such as =MAX(A4:A5) + TODAY() – TODAY(). Another is to use VBA as fastexcel advised me. “Probably the simplest bypass is to do a single Application.CalculateFull after importing all the additional data.” And another is to do Ctrl-Alt-F9. Yet all these methods are a sort of Band-Aid for the issue though.
I had many formulas already in existence that were created in XL2003 with the non-Table Reference syntax. So when I upgraded to 2007 everything worked OK and life was good. Hooooowwwwwwwever, when I upgraded to XL2010, the failure to recalculate occurred and I didn’t detect the problem for a few months. I was up the creek for I while, until I had hunted down and corrected files that were in my customers’ possessions. What a pain and an embarrassment. Anyways, I was able to open a formal MS support case, and the MS tech support engineering staff submitted it to their programmers as an “official” bug. I am hopeful for a hotfix before XL2015, realizing that XL2013 is now RTM, released to manufacture.
Me gizzzzzmo again,
IMHO, any time I create a formula that says one cell equals another, such as entering in Cell A2 =A1, it should. And when it don’t, it ain’t right. That’s my KISS point of view.
Hi,
A somewhat unrelated table behaviour reported by bobbyscar also seems buggy to me. It is described here: http://www.mrexcel.com/forum/excel-questions/665403-calculated-column-error-tables.html
I’m not sure if it is already a well-known issue that has been noted elsewhere but it did seem buggy to me.
As of 24 May 2013 this bug is still there in Excel 2010 and Excel 2013