XL: ERROR.TYPE Help Example Returns Incorrect Result

 Last reviewed: February 5, 1998
Article ID: Q179379
The information in this article applies to:
  • Microsoft Excel 98 Macintosh Edition
  • Microsoft Excel 97 for Windows
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a
  • Microsoft Excel for Windows, versions 5.0, 5.0a, 5.0c
  • Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SYMPTOMS

When you use the ERROR.TYPE function, the value you expect is not returned according to the example in 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 Help returns #N/A error rather than the value of the cell you are testing.

CAUSE

This problem 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 problem, use the ISERROR function to determine if the ERROR.TYPE function is returning an error. If the function returns an error, 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 this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

The Example in Microsoft Excel Help is as follows: 

   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.

This function was first included in Microsoft Excel 5.0. In this version of Excel, the function is a macro function that can also be used on a worksheet. The example given in Excel versions 5.0 and 7.0 is as follows: 

   The following macro formula checks the cell named Ratio to see if it
   contains a #DIV/0! error value. If it does, a subroutine named
   DivisionByZero is run.

   IF(ERROR.TYPE(Ratio)=2,DivisionByZero())
However, this example does not work because a subroutine cannot be run from a worksheet statement. This formula works if there is an error in the cell and DivisionByZero is a user-defined function; however, the formula fails if there is a value in the cell.

If you are using this example, use the following formula to replace the example in Help: 

   =IF(ISERROR(ERROR.TYPE(Ratio)),Ratio,IF(ERROR.TYPE(Ratio)=2,
   DivisionByZero(),""))

Additional query words: XL97 XL5 XL7
Keywords : xlformula
Version : MACINTOSH:5.0,5.0a,98; WINDOWS:5.0,5.0a,5.0c,7.0,7.0a,97
Platform : MACINTOSH WINDOWS
Issue type : kbdocerr
Solution Type : kbpending

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 5, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.