How to correct a #VALUE! in Excel

How to correct a #VALUE! in Excel


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

How to fix value in Excel 2

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

How to fix value in Excel 3

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

How to fix value in Excel 4

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.

How to fix value in Excel 5

After the date value in cell C3 is revised, the formula will work and return the expected result:

How to fix value in Excel 6

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)

How to fix value in Excel 7

After the date in cell B3 is corrected, the #VALUE! Error no more:

How to fix value in Excel 8

https://thuthuat.taimienphi.vn/cach-sua-loi-value-trong-excel-46559n.aspx
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.

.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *