1. Using Excel SUM Function to Remove #VALUE! Error
- In the example below, the #VALUE! occurs because there is a text in cell D6.
- Here, you can use the SUM function instead of using the addition operator (+) to get the total.
- Replace the formula in cell F6 with the following one. This will ignore text values in the cell range and remove the #VALUE! Error from the output cell.
=SUM(C6:E6)
Read More: [Fixed!] VALUE Error in Excel
Method 2 – Use the IFERROR Function in Excel to Remove #VALUE! Error
- In the example below, cell D6 is empty. Excel takes the values of empty cells as 0, and Excel should not show any error here. So, why is it showing a #VALUE! error here?
- Because cell D6 is not actually empty although it appears to be empty. It contains some spaces in there. This is why Excel is displaying the #VALUE! error.
- Here, we will use the IFERROR function to remove the error. Apply the following formula in cell F6 to fix the error.
=IFERROR(C6+D6+E6,"Check Data")
Method 3 – Removing #VALUE! Error with IF & ISERROR Functions
- In the following example, the #VALUE! error occurs because the date in cell B6 is not formatted properly.
- You can use the IF and ISERROR functions together to remove the #VALUE!. Change the formula in cell D6 with the one below to remove the error.
=IF(ISERROR(C6-B6),"Invalid",C6-B6)
Method 4 – Using Excel PRODUCT Function to Remove #VALUE! Error
- In the following example, the #VALUE! error occurs because of the text in cell C6.
- Use the PRODUCT function to multiply only the numeric values. This function ignores text and other non-numeric entries. So if your input cells have any value other than a number, you can use this function to avoid #VALUE! error.
- Use the formula below in cell F6. Excel will not show the #VALUE! error anymore.
=PRODUCT(C6:D6)
Download Practice Workbook
You can download the practice workbook from the download button below.
Related Articles
- How to Calculate Mean Squared Error in Excel
- How to Calculate Root Mean Square Error in Excel
- [Fixed!] NUM Error in Excel
- Excel Error: The Number in This Cell is Formatted as Text
<< Go Back To Excel Formula Errors | Errors in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
