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

Article translations Article translations
Article ID: 272864 - View products that this article applies to.
This article was previously published under Q272864
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

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.

Properties

Article ID: 272864 - Last Review: November 2, 2013 - Revision: 4.0
Applies to
  • Microsoft Excel 2004 for Mac
  • Microsoft Excel X for Mac
  • Microsoft Excel 2001 for Mac
Keywords: 
kbnosurvey kbarchive dftsdahomeportal kbnofix kbprb KB272864

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com