You are currently offline, waiting for your internet to reconnect

#VALUE error when you add text and values in Excel

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

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 equivalentworksheet 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.
XL2002 XL2000 XL97
Properties

Article ID: 100825 - Last Review: 01/18/2007 22:40:08 - Revision: 3.1

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • kbprb KB100825
Feedback