In learning about Excel, Taimienphi.vn will share how to fix a #VALUE! in Excel. Also readers can refer to some other articles already on Taimienphi.vn to learn how to fix Circular Reference error or error #NAME? in Excel.
There are many causes error #VALUE! in Excel, may be invalid value di, …. Refer to the following article of Taimienphi.vn to learn how to fix #VALUE! in Excel.
1. Error #VALUE! in Excel
Error #VALUE! in Excel appears when a value is not a valid value type. Error can occur if cells are left blank, a function returns text values, or if date values are entered as text. To fix the #VALUE! Error In Excel, just enter the correct value type.
Compared to other Excel errors, the #VALUE error is a bit more complicated because some functions automatically ignore invalid data. The SUM function, for example, only ignores text values, but regular addition and subtraction with the addition (+) minus (-) operators will return the #VALUE! Error. If any value is text.
2. How to correct a #VALUE! Error in Excel
To better understand the cause of #VALUE! In Excel and how to fix specific errors, read along with some examples of Taimienphi.vn.
Example 1: Invalid text value
In the example below, cell C3 contains the text “NA” and cell F2 returns the #VALUE !: error.
= C3 + C4 // return the #VALUE! error
To fix the #VALUE! Error, the solution is to enter the missing value in cell C3. Now the formula in cell F3 will work and return the correct result:
= C3 + C4 // return value 6
Also in this case, we can fix the #VALUE! Error. by passing the SUM function. The SUM function automatically ignores the text values:
= SUM (C3, C4) // return the value 4.5
Example 2: The function argument is not a valid value type
Error #VALUE! may also occur in cases where the function argument is not the desired value type.
In the example below, the NETWORKDAYS function is set 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 revised, the formula will work and return the expected result:
Example 3: The date is saved as a text
Sometimes spreadsheets may contain invalid dates, as these date values are stored as text. In the example below, the EDATE function is used to calculate the expiry date from 3 months after the date of purchase. The formula in cell C3 returns the #VALUE! Error. because the date in cell B3 is saved as text (i.e. the date format is invalid):
= EDATE (B3,3)
After the date in cell B3 is corrected, the #VALUE! Error no more:
The article above Taimienphi.vn has shown you how to fix the #VALUE! Error. In Excel, you refer to how Correct a # N / A error in Excel. In addition, if you have any questions or questions, you can leave your comments in the comment section below the article.