#VALUE error when you add text and values in Excel

Article translations Article translations
Article ID: 100825 - View products that this article applies to.
This article was previously published under Q100825
Expand all | Collapse all

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.

Properties

Article ID: 100825 - Last Review: January 18, 2007 - Revision: 3.1
APPLIES TO
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
Keywords: 
kbprb KB100825

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com