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.