Q117126 - XL5: 3-D Syntax in Array Formula Calculated Incorrectly

XL5: 3-D Syntax in Array Formula Calculated Incorrectly

The information in this article applies to:

Microsoft Excel for Windows, version 5.0
Microsoft Excel for the Macintosh, version 5.0

In Microsoft Excel version 5.0, a formula can refer to a range that spans two or more sheets in a workbook. This is called a 3-D reference. You cannot use a 3-D reference in an array formula. In addition, if you enter a 2-D reference in the 3-D reference syntax in an array formula, as in the following example


   =SUM(IF(Sheet1!A1:Sheet1!A3="a",1,0))

NOTE: The above formula must be entered as an array formula. To enter a formula as an array formula in Microsoft Excel for Windows, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+ENTER.

Note that if you use the same sheet name for both the starting cell and the ending cell of the reference, as in the above example, Microsoft Excel will treat this as a 2-D formula as long as that workbook remains open. However, when you close and reopen the workbook, the formula is treated as a 3-D reference and is calculated incorrectly.

Because the first and last sheet in this reference refers to the same worksheet, it is not a 3-D reference. To create a formula that will be correctly recalculated, you can use a 2-D reference as in the following example:


   Sheet1!A1:A3

WORKAROUND

To work around this problem, change the formula in cell A5 to a 2-D formula as follows:


   =SUM(IF(sheet1!A1:A3="a",1,0))

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel version 5.0c.

REFERENCES

"User's Guide," version 5.0, pages 135-137, 171-174

Additional query words: 5.00a

Keywords :
Version : 5.00
Platform : WINDOWS
Issue type :
Technology :