XL5: 3-D Syntax in Array Formula Calculated Incorrectly

• 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 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