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

• Microsoft Excel for Windows, version 5.0

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 Microsoft Excel version 5.0 for Windows. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

REFERENCES

For more information about using arguments in functions, choose the Search button in Help and type:

   arguments, function