Microsoft KB Archive/272864

= No calculation occurs when an array formula includes a large range in Excel for Mac =

Article ID: 272864

Article Last Modified on 1/29/2007

-

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 &quot;Calculating Cells 0%.&quot; 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.

Additional query words: MacXLX Mac XLX off2001 XL2001 excel update formula calculate modify

Keywords: kbnofix kbprb KB272864

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.