Microsoft KB Archive/100825

= #VALUE error when you add text and values in Excel =

Article ID: 100825

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q100825



SYMPTOMS
In Microsoft Excel, when you perform a mathematical operation on cells that contain text and values, you may receive a #VALUE! error.



CAUSE
Although some functions correctly evaluate cells and ignore text strings, if you add the cells by using arithmetic operators such as addition (+), subtraction (-), multiplication (*), or division (/), an error value may occur.



WORKAROUND
Instead of using an individual mathematical operator, use its equivalent worksheet function instead:

SUM (adds)

PRODUCT (multiplies)

QUOTIENT (divides)

For example, if you type the following information in cells A1:A7 of a worksheet   A1: 10 A2: text A3: 20 A4: =A1+A2+A3 A5: =SUM(A1+A2+A3) A6: =SUM(A1,A2,A3) A7: =SUM(A1:A3) the formulas in cells A4 and A5 return a #VALUE! error; however, cells A6 and A7 return the correct value of 30.

Additional query words: XL2002 XL2000 XL97

Keywords: kbprb KB100825

-

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

© Microsoft Corporation. All rights reserved.