Microsoft KB Archive/118466

{|
 * width="100%"|

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

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, version 5.0
 * Microsoft Excel for Windows, version 7.0
 * Microsoft Excel 97 for Windows

-

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.