Microsoft KB Archive/50478

FORMULA.ARRAY Requires RxCy Format PSS ID Number: Q50478 Article last modified on 02-26-1993 PSS database name: W_eXceL

2.x 3.00 | 2.20 3.00

WINDOWS | OS/2

Summary:

When using the FORMULA.ARRAY command, a formula entered into a range of cells must use R1C1-type references, even if the spreadsheet into which the formula will be entered is using A1 format. If the spreadsheet does use A1-type references, Excel converts the formula to the correct format before entering it into the spreadsheet.

Using A1-type references with a formula (which must be in quotation marks) with FORMULA.ARRAY results in the run-time macro error “Formula is not valid,” and #VALUE is returned. For example, the following generates an error:

=FORMULA.ARRAY(“=A1:A10+B1:B10”,C1:C10)

Changing it as follows corrects the error:

=FORMULA.ARRAY(“=R1C1:R10C1+R1C2:R10C2”,C1:C10)

The same holds true when using the FORMULA statement.

Reference(s):

“Microsoft Excel Function Reference,” version 3.0, page 90

Copyright Microsoft Corporation 1993.