Exceljet

Quick, clean, and to the point

How to fix the #VALUE! error

Excel formula: How to fix the #VALUE! error
Explanation 

The #VALUE! error appears when a value is not the expected type. This can occur when cells are left blank, when a function expecting a number is given a text value, and when dates are evaluated as text by Excel. Fixing a #VALUE! error is usually just a matter of entering the right kind of value.

The #VALUE error is a bit tricky because some functions automatically ignore invalid data. For example, the SUM function just ignores text values, but regular addition or subtraction with the plus (+) or minus (-) operator will return a #VALUE! error if any values are text.

The examples below show formulas that return the #VALUE error, along with options to resolve.

Example #1 - unexpected text value

In the example below, cell C3 contains the text "NA", and F2 returns the #VALUE! error:

=C3+C4 // returns #VALUE!

#VALUE! error example - unexpected text value

One option to fix is to enter the missing value in C3. The formula in F3 then works correctly:

=C3+C4 // returns 6

#VALUE! error example - unexpected text value FIX 1

Another option in this case is to switch to the SUM function. The SUM function automatically ignores text values:

=SUM(C3,C4) // returns 4.5

#VALUE! error example - unexpected text value FIX 2

Example #2 - function argument not expected type

The #VALUE! error can also arise when function arguments are not expected types. In the example below, the NETWORKDAYS function is set up to calculate the number of workdays between two dates. In cell C3, "apple" is not a valid date, so the NETWORKDAYS function can't compute working days and returns the #VALUE! error:

#VALUE! error example - function argument not expected type

Below, when proper date is entered in C3, the formula works as expected:

#VALUE! error example - function argument not expected type - FIX

Example #3 - dates stored as text

Sometimes a worksheet will contain dates that are invalid because they are stored as text. In the example below, the EDATE function is used to calculate an expiration date three months after a purchase date. The formula in C3 returns the #VALUE! error because the date in B3 is stored as text (i.e. not properly recognized as a date):

=EDATE(B3,3)

#VALUE! error example - date stored as text

When the date in B3 is fixed, the error is resolved:

#VALUE! error example - date stored as text FIXED

If you have to fix many dates stored as text, this page provides some options for fixing.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.