Microsoft KB Archive/100825

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 15:02, 18 July 2020 by 3155ffGd (talk | contribs) (importing KB archive)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Knowledge Base


#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