No calculation occurs when an array formula includes a large range in Excel for Mac (272864)

The information in this article applies to:

Microsoft Excel 2004 for Mac
Microsoft Excel X for Mac
Microsoft Excel 2001 for Mac

This article was previously published under Q272864

SYMPTOMS

When you use an array formula based on a very large range (For example, over 25 columns and 1,000 rows), Microsoft Excel for Mac does not return a result. The cells do not appear to calculate and the status bar displays "Calculating Cells 0%." Additionally, worksheet navigation may become sluggish.

WORKAROUND

To work around this behavior, select several smaller ranges, and then combine the results. For example, the array formula

{=sum(if(A1:Z5000<=.5,1,0))}

returns the number of cells in A1:Z5000 that have a value that is less than or equal to .5. The result can also be obtained by finding the sum of the result of 26 array formulas, one for each column (for example, {=sum(if(A1:A5000<=.5,1,0))} + {=sum(if(B1:B5000<=.5,1,0))} + and so on).

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

For more information about how to create an array formula, click the Office Assistant, type About array formulas and how to enter them, click Search, and then click a topic to view it.

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar.

Modification Type:	Minor	Last Reviewed:	2/15/2005
Keywords:	kbnofix kbprb KB272864