Buzz
There are several reasons behind the #VALUE! error in Excel, including invalid data types, ... . Continue reading Mytour's article below to learn how to fix the #VALUE! error in Excel.
Fixing the #Value! Error in Excel
1. #VALUE! Error in Excel.
2. How to fix #VALUE! Error in Excel.
Guidelines on addressing #Value! error in Excel
1. #VALUE! Error in Excel
The #VALUE! error in Excel occurs when a value is not a valid type. The error can occur if cells are left blank, a function returns a text value, or if date values are entered as text. To fix the #VALUE! error in Excel, simply input the correct value type.
Compared to other Excel errors, the #VALUE error is a bit more complex because some functions automatically ignore invalid data. For example, the SUM function only ignores text values, but regular addition and subtraction operations with the plus (+) and minus (-) operators will return a #VALUE! error if any value is text.
2. How to fix the #VALUE! error in Excel
To better understand the reasons behind the #VALUE! error in Excel and how to fix each specific cause, readers can refer to some examples provided below by Mytour.
Example 1: Invalid text value
In the example below, cell C3 contains the text 'NA' and cell F2 returns the #VALUE! error:
=C3+C4 // returns #VALUE!
To fix the #VALUE! error, the solution is to input the missing value in cell C3. At this point, the formula in cell F3 will work and return the correct result:
=C3+C4 // returns the value 6
In addition, in this case, we can fix the #VALUE! error by switching to the SUM function. The SUM function automatically ignores text values:
=SUM(C3,C4) // returns the value 4.5
Example 2: Invalid function argument type
The #VALUE! error can also occur if the function argument is not the desired value type.
In the example below, the NETWORKDAYS function is set up to calculate the number of working days between 2 dates. In cell C3, 'apple' is not a valid date value, so the NETWORKDAYS function cannot calculate the number of working days and returns the #VALUE! error:
After the date value in cell C3 is corrected, the formula will work and return the expected result:
Example 3: Dates stored as text
Sometimes, spreadsheets may contain invalid dates because these date values are stored as text. In the example below, the EDATE function is used to calculate the expiration date 3 months after the purchase date. The formula in cell C3 returns the #VALUE! error because the date in cell B3 is stored as text (meaning it has an invalid date format):
=EDATE(B3,3)
After the date in cell B3 is corrected, the #VALUE! error will no longer appear:
In the above article, Mytour has just guided you on how to fix #VALUE! errors in Excel, you can also refer to how to fix #N/A errors in Excel. Additionally, if you have any doubts or questions that need clarification, readers can leave their comments below the article for assistance.
