The #value error is an error in Excel that indicates the formula used is incorrect. The error commonly occurs when the value type is not one of the arguments of the formula. To new users, locating and finding the cause of the #value error may be challenging and time-consuming. This article will discuss the scenarios likely to result in a #value error and how to fix the error.
Text Strings in the Formula
Excel has numerous formulas that are used in modifying the dataset. However, these datasets differ in the data type of their arguments. Some formulas use strings as the datatype, while others use integers as the arguments. An error can occur if strings are used when the formula requires an integer as the argument. Below is an example:
1. Enter the dataset on the empty cells.
2. Replace one value of the dataset with a string.
3. Locate an empty cell and add this formula, =B1+B2+B3
4. Finally, hit the Enter button. The result of the formulae will be #value
The formula results in a #value error because we used a string in an integer formula.
How to fix errors caused by string
We can use other inbuilt formulas to ignore the error caused by the string. Below are steps to use the Sum function to ignore the #value caused by strings.
1. Enter the dataset on the empty cells.
2. Replace one value of the dataset with a string.
3. Locate an empty cell and type the SUM function. That is =SUM
4. Select all the cells with your dataset. For example, =SUM (B1:B3)
5. Finally, hit the Enter button. The result of the formulae will be displayed in the selected cell.
Use of Special characters
Special characters are essential to an Excel document. However, in some cases, they may result in an #value error.
How do you get an error?
1. Enter the dataset on the empty cells.
2. Try adding a special character to one of the datasets
3. Locate an empty cell and add this formula, =B1+B2+B3
4. Finally, hit the Enter button. The result of the formulae will be #value
How to fix errors caused by Special Character
Below are the steps to follow:
1. Enter the dataset on the empty cells.
Note: You should not have special characters in the dataset.
2. Locate an empty cell and type the SUM function. That is =SUM
Incorrect date formats
Use of incorrect date format can result in a #value error. Below is an example that can lead to this error.
1. Enter the dataset on the empty cells. Let us use the date value in string format.
2. Locate an empty cell and add this function, =Date(B2)
3. Finally, hit the Enter button. The result of the formulae will be #value
How to fix error:
Steps:
1. Locate the cell with the #value error.
2. Double-click to open the formula. Delete the existing function.
3. Add the IFFERROR function with the date function as the argument. For Example =IFERROR(DATE(B1,B2,B3), "Check date")
4. Finally, hit the Enter button.