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

ID: Q118466

5.00 WINDOWS

The information in this article applies to:

Microsoft Excel for Windows, version 5.0
Microsoft Excel for Windows 95, 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.

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.

KBCategory: KBSubcategory:

Additional reference words: 5.00 analysf.xla xl xl97

Version           : 5.00
Platform          : WINDOWS

Last Reviewed: September 23, 1998