Result of ISNUMBER() Displayed Incorrectly in the Formula Bar

 Last reviewed: November 2, 1994
Article ID: Q70219

SUMMARY

If you highlight the formula ISNUMBER(value) in the formula bar and choose Calculate Now (F9), FALSE will be displayed when "value" is an external reference to a closed worksheet, regardless of whether the argument "value" refers to a number, to a blank cell, or to a cell containing text.

MORE INFORMATION

This information refers only to Excel 3.00. Versions earlier than 3.00 do not allow you to use the IS functions with external references to closed documents.

ISNUMBER() will be calculated correctly if the underlying worksheet is open.

Example

  1. Place a "10" (without the quotation marks) in cell A1 of SHEET1.XLS.

  2. Close SHEET1.XLS.

  3. Enter the following into cell A1 of SHEET2.XLS: 

          =IF(ISNUMBER('C:\EXCEL\SHEET1.XLS'!$A$1),1,0)

    NOTE: Modify this path to indicate the location of SHEET1.XLS.

  4. Press ENTER.

  5. Cell A1 will contain the value 1 that is correct.

  6. Place your cursor in cell A1 of SHEET2.XLS and press F2.

  7. Highlight the following: 

          ISNUMBER('C:\EXCEL\SHEET1.XLS'!$A$1)

  8. Choose Calculate Now (F9).

  9. FALSE will be displayed in place of the function ISNUMBER(value). This is incorrect. The result of the ISNUMBER() function is TRUE.

Microsoft is researching this problem and will post new information here as it becomes available.

REFERENCES

"Microsoft Excel Function Reference." Version 3.00, pages 135-136.

KBCategory: kbother
KBSubcategory:

Additional reference words: noupd

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: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.