Array formulae do not recalculate

[Thanks]

I have a large number of array formulae in my sheet. I update the supporting
data and the formulae do not recalculate. The only way i can get a recalc is
to F2 and then ctrl+shift+enter. How can I get all of the formulae to
recalc. Manual recalc does not work.

 

[Dave Peterson]

Does Automatic Calculation mode work?

If excel is confused about whether it should recalculate a formula, this
sometimes wakes excel up and stops the confusion.

Select all the cells on that sheet
(ctrl-a a few times should do it)

Edit|replace
what: = (equal sign)
with: =
replace all

Excel will see that you're changing all your formulas and know to recalc each
because of the change.

 

[Charles Williams]

You could try
Ctrl-Alt-F9 to force a Full calculation

or if the dependency tree is messed up you can rebuild it using
ctrl-shift-alt-F9 - sometimes after this a normal F9 will work.

If this does not work you may have hit one of the array formulae
limits.

For excel versions prior to Excel 2007 there is a limit to the number
of array formulae that can refer to other sheets.

from KB 166342

Maximum array formulas
In Excel 2003 and in earlier versions of Excel, a single worksheet may
contain a maximum of 65,472 array formulas that refer to another
worksheet. If you want to use more formulas, split the data into
multiple worksheets so that there are fewer than 65,472 references to
a single worksheet.

For example, in Sheet1 of a workbook, you can create the following
items:

* 65,472 array formulas that refer to Sheet2
* 65,472 array formulas that refer to Sheet3
* 65,472 array formulas that refer to Sheet4

(If you are using multi-cell array formulae I suspect that the limit
is actually 65472 cells)

regards
Charles