This article is all about the #VALUE! error in Excel and the different ways to fix it. We will also look at the reasons why we encounter a #VALUE! in Excel in this tutorial.
What is #VALUE! error?
A #VALUE! error is displayed in an Excel spreadsheet when a value or formula is not the expected type. You are seeing this error probably because you entered a text value instead of a number value in a formula or a number value for a date value in a formula.
When does the #VALUE! error occur?
The #VALUE! error occurs due to multiple causes. Let us look at the different instances when this occurs in Excel.
1. Summing text and numerical values together
You can see that a series of cells that include text, date, and numerical values were summed up manually in the cell below. On pressing ENTER on the keyboard, the #VALUE! error shows up.
This happens because Excel is looking for a set of numerical or date values only to add them together and give an answer based on that. Excel is unable to add the values together because of a text value in between.
Note that no #VALUE! error is displayed when you sum these values using the SUM function in Excel. The SUM function is smarter and automatically removes all text values in the calculation.
2. Summing text values
In the image above, we have summed up three text values in three different cells together. Again, we didn’t use the SUM function here, and instead, we summed each value manually putting a + sign in the middle. As a result, we see a #VALUE! error.
The reason is simple. Excel cannot sum text values together. If you had used the SUM function to sum these text values together, the result would be 0 because it didn’t find any summable values in any cell.
The result will be the same if you try to find the difference between these values and almost any mathematical function in Excel.
3. Multiplying numbers with text or blank cells
In this image above, we are trying to multiply a text value with a number and the result is a #VALUE! error on pressing ENTER. Similarly, if you try to multiply a number with a blank cell in Excel you get the #VALUE! error.
A blank cell denotes a cell with an unnecessary space in an empty cell. A space in between formulas also gives a #VALUE! error in Excel.
4. Summing and multiplying ranges
In this image, we are trying to sum and multiply the two ranges but we get the result as a #VALUE! error. The reason is that the SUM function cannot add and multiply two ranges at a time. You need to use the SUMPRODUCT formula for that.
The SUMPRODUCT formula multiplies the adjacent values of corresponding ranges and returns a sum of it. However, the trick is to make the SUM formula work without having to use the SUMPRODUCT function is to press CTRL+SHIFT+ENTER before finishing the formula.
5. Providing a text value to the NETWORKDAYS function
The NETWORKDAYS function is used to count the working days between two dates. In the example above, we have provided a text value with a date value for the function that is unable to count the days and is giving the result as a #VALUE! error.
6. Creating an illogical IF formula
You can see that we did not provide any appropriate logical text for the IF formula to give an appropriate true or false value. You see a #VALUE! error displaying on pressing ENTER.
Tips to fix the #VALUE! error for different cases
Here are the tips to fix #VALUE! error in Excel for different instances like these.
1. Summing text and numerical values together
When you sum numbers manually without using the SUM function, make sure to add numbers with numbers or date values only based on your preferences and date values with date values only to avoid getting the #VALUE! error.
2. Summing only text values
Excel cannot add or subtract text values and will display the #VALUE error when you sum, multiply or subtract them manually. However, to avoid getting the error, use built-in formulas like SUM, PRODUCT, SUMPRODUCT, etc. to let Excel ignore a text or any other values that cannot be quantified.
3. Multiplying numbers with text or blank cells
If you have a blank space in a cell where you are applying a formula that is showing a #VALUE! error. Make sure to remove all unnecessary spaces from cells while you are applying a formula to a set of values.
If the dataset is too large to be modified, consider using the TRIM function on your datasets to remove all extra spaces and mistakes from them.
4. Summing and multiplying ranges
You cannot normally sum and multiply two ranges using the SUM formula in Excel. To be able to sum and multiple ranges at the same time, you can use the SUMPRODUCT function to multiply the corresponding ranges and give a sum of them.
Or, press CTRL+SHIFT+ENTER before finishing the SUM formula to make it work like the SUMPRODUCT formula.
5. Providing a text value to the NETWORKDAYS function
The NETWORKDAYS function requires two date values to count the number of working days between them. Avoid entering text or numerical values and end getting a vague answer or the famous #VALUE error.
6. Creating an illogical IF formula
Make sure you provide the IF function with an appropriate logical test. Here’s is an example of a correct IF function.
Here, we are creating a condition that if the values in the range are less than 20, then display “Low” in the active cell, if not, display “High” in the active cell.
Note that we have changed the values to help you understand how an IF function works.
Read more about the IF function in detail here.
Conclusion
This article was all about the #VALUE! error in Excel and the different ways to fix it. Stay tuned for more amazing Excel tutorials like this only on QuickExcel!
References: ExcelJet.