Microsoft KB Archive/213891

= XL2000: Excel Help Example Returns Incorrect Result for ERROR.TYPE Function =

Article ID: 213891

Article Last Modified on 9/27/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q213891





SYMPTOMS
When you use the ERROR.TYPE function in Microsoft Excel, you may not receive the value that you expect, according to the example in Excel Help. If the cell that is being compared has an error value, the example works properly; however, if this cell contains a value (a number or text), the example in Excel Help returns a #N/A error rather than the value of the cell that you are testing.



CAUSE
This behavior occurs when you evaluate a cell that does not match one of the expected error types, as in the following example:

A1: 5

A2: 10

A3: =IF(ERROR.TYPE(A1)=7, "Value is not available", A2)

Because A1 contains a value and not an error, the ERROR.TYPE function returns a #N/A error instead of the condition specified in the IF argument, which is the contents of cell A2.



WORKAROUND
To work around this issue, use the ISERROR function to determine if the ERROR.TYPE function is returning an error. If the function returns an error, take steps to return a value instead. For example, replace the formula in the "Cause" section with the following formula:

=IF(ISERROR(ERROR.TYPE(A1)),A2,IF(ERROR.TYPE(A1)=7,"Value is not available",""))



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



MORE INFORMATION
The example in Excel Help states the following:

The following formula checks cell E50 to see whether it contains a #N/A error value. If it does, the text "Value is not available" is displayed. Otherwise, the value in E50 is returned.

IF(ERROR.TYPE(E50)=7, "Value is not available", E50)

As the syntax shows, the ERROR.TYPE function returns a #N/A error if it refers to a cell that contains a proper value. This error also causes the IF statement to return such a value. You cannot use this function this way without first trapping the error returned by ERROR.TYPE.

Keywords: kbbug kbpending KB213891

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.