In excel, many times we get #VALUE error. This error simply means that the variable you have supplied is not of a supported type. As per Microsoft official site, a “#VALUE is Excel's way of saying, there's something wrong with the way your formula is typed. Or, there's something wrong with the cells you are referencing”.
Let’s understand it with some examples
#VALUE due Arithmetic Operators
The most basic reason for #Value error is when the operation is done using Arithmetic Operators and values can be non-numeric values.
For example, adding 1 with NA (1+NA) or any other text. This will cause an #VALUE error.
To avoid this we should use the SUM() function. This function ignores non-numeric values and sums numeric values only. Hence no #VALUE ERROR.
See below screenshot.
In the first table, I have used A2+B2, A3-B3, and A4*B4. Since B column has some non-numeric values, we get #VALUE error.
In the next table, we have used the equivalent function of arithmetic operators to do the same operations, like SUM and PRODUCT function. The SUM function treats non-numeric values as 0 and PRODUCT as 1, so that they don’t affect the result.
Cells Having Spaces Only
Sometimes, cells look blank but they are actually are not. When you try to do some operation with these cells, you may get #VALUE error or incorrect results.
Here B2 is blank and it should be treated as 0 but you can see it is returning #VALUE error. It means that B2 is not blank.
Many-times cells have spaces. To make sure that if a cell is blank or not, use the ISBLANK function.
Once you know which cells are not blank, delete cell value by pressing the Delete button.
You can use find and replace (CTRL+H) to replace all space with nothing.
Convert Text Formated Dates into Dates
When we get data from others, the dates are not well formated, mostly they are formated as text. This happens a lot when you merge data from different sources. Before working on dates, format them as dates. They also generate #VALUE error.
To make the dates, press CTRL+1 to open cell formatting option. Click on Date.
Choose your favourite date style and hit the OK button.
Now they are dates.
So yeah, this is the way to convert handle #VALUE errors. If you have any query, feel free to use the comment section below.
Related Articles:
Create Custom Error Bars in Excel 2016
How to Trace and Fix Formula Errors in Excel
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity
How to Use SUMIF Function in Excel
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
