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

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:
Platform : WINDOWS
Issue type :


Last Reviewed: November 20, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.