Excel: Array Formula with GOTO or RETURN Doesn't Execute

ID: Q47010

The information in this article applies to:

SUMMARY

In Microsoft Excel, if a COMMAND+ENTERed array formula contains certain macro action-invoking functions, for example, GOTO or RETURN, the functions are not executed.

To correct this problem, break the formula into two separate statements. The array portion of the function should be COMMAND+ENTERed into one cell, with the portion containing the GOTO or RETURN entered into another cell. The result of the array formula can be passed as a parameter to the cell containing the GOTO or RETURN by referring to the reference of the COMMAND+ENTERed cell.


MORE INFORMATION

For example, when the following array formula is COMMAND+ENTERed, it returns the average of all the numbers in the range A1:A10 that are greater than 5: 


   =AVERAGE(IF(A1:A10>5,A1:A10))


To use this result in a conditional branching statement later in the macro, refer to this cell. For example, if you want to branch the operation to cell B1 if this average is greater than 20, use the following statement, where "ref" is the reference to the cell containing the AVERAGE formula:

=IF(ref>20,GOTO(B1))

Additional query words: 1.0 1.00 1.03 1.04 1.06 1.5 1.50 2.20 3.0 4.00 


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: March 15, 1999