XL5: ATP Functions Return #VALUE! Using Array as Argument

The information in this article applies to:

SYMPTOMS

When you use an array as an argument in an Analysis ToolPak function, you will receive the #VALUE! error value.

This behavior is different from earlier versions of Microsoft Excel (which allowed you to pass arrays as arguments).


CAUSE

The Analysis ToolPak in Microsoft Excel 5.0 detects the type of data being passed and only accepts single values, unless arrays are required or optional to the function used. If the function expects a single value, and an array is passed, an error will be returned.

Example


   =MROUND(3,2)        Returns 4
   =MROUND({3,4,5},2)  Returns #VALUE!


WORKAROUND

For functions that return the #VALUE! error value, you will need to use the INDEX() function to use specific elements of the array argument. For example, with MyArray equal to {3,4,5}, =MROUND(INDEX(MyArray,1),2) will be evaluated as MROUND(3,2), and will return the value 4.


STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.


REFERENCES

For more information about arguments in functions, click Contents And Index on the Help menu, click the Index tab in <application> Help, type the following text

arguments, array formulas
and then double-click the selected text to go to the "About array formulas and how to enter them" topic. If you are unable to find the information you need, ask the Office Assistant.

Additional query words: analysf.xla xl xl97

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


Last Reviewed: April 12, 2000
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.