XL97: Calculation of Formula in Formula Bar Returns #VALUE!

 Last reviewed: January 15, 1998
Article ID: Q157031
The information in this article applies to:
  • Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel 97, when you press F9 to evaluate a formula in the formula bar, the formula may return #VALUE! even when the formula returns the correct result to the cell.

CAUSE

This behavior occurs if the formula returns a string that contains more than 255 characters.

STATUS

This is by design in Microsoft Excel 97.

MORE INFORMATION

In Microsoft Excel, you can select all or part of a formula in the formula bar and press F9 to evaluate only the selected portion. If the selected portion returns a string that contains greater than 255 characters, it returns #VALUE!.

For example, if you type the following formula in a cell: 

   =REPT("a",1000)
and you select the formula in the formula bar and press F9, the formula returns #VALUE! instead of a string that contains 1000 "a" characters as expected.

The following text functions may evaluate to #VALUE! if they return more than 255 characters: 

   CLEAN
   CONCATENATE
   LEFT
   LOWER
   MID
   PROPER
   REPLACE
   REPT
   RIGHT
   SUBSTITUTE
   TRIM
   UPPER

Additional query words: XL97 8.0 8.00 string function recalculate
calculate
Keywords : xlformula
Version : WINDOWS:97
Platform : WINDOWS

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