Error messages are unavoidable when dealing with Excel, and you may be surprised when they suddenly appear, but there is nothing to fear if you know what they mean and what to do about them.
Here are the meanings of this error and countermeasures.
Prerequisite.
As an example, we have created this table.
Column "B" all contain the same number "1."
Column "D" contains "B+C."
For example, the "D2" cell contains the following formula.
=B2+C2In addition, the "D3" cell contains the following formula
=B3+C3
Column "D" below contains the formula B+C.
Examples of errors and non-errors
First, let's look at the "D2" cell as a basic form that is error-free.
The "B2" and "B2" cells contain the numbers "1" and "2" respectively, so the "D2" cell shows "3" with 1+2=3.
If there is a blank in the calculation process
If you look closely at the table, you will see that cell "D3" has the error "#VALUE!", but cell "D4" has no error. The difference is in the "C" cell, so let's take a look at its contents.
The "C3" cell contains the following, and the calculation results are shown as """The "blank" is now displayed.
=IF(A3="",""",1)
However, the "C4" cell is really "blank" with nothing in it.
In other words, a blank space is treated the same as a "0" and the calculation result of 1+0=1 is displayed, but if you try to force the display of a """ blank space in the calculation result, you will get the error "#VALUE!
Measures to be taken when there are gaps in the calculation process
There are many ways to deal with this problem, but the way you deal with it depends on how you want to do it.
If there is a blank space, the message is sent out without calculation.
This one assumes that the "C3" cell is """ blank and devises a function to put in the "D3" cell.
If you want to generate a message when the calculation result of "B3+C3" is an error, use the "IFERROR function" to generate a message.
= IFERROR(B3+C3, "No value in XXX, please enter □□")Suppose that
If you want to generate a message if cell "C3" is "" blank, use the "IF function".
=IF(C3="", "XXX has no value, please enter □□",B3+C3)and should be
The result in both cases was
" is displayed.
If it is blank, treat it as "0".
In this case, the result of the function in "Column C" should display "0" zeros instead of "" blanks.
The function entered in cell "C3."
=IF(A3="",""",1)from (e.g. time, place, numerical quantity)
=IF(A3="","0,1)Rewrite to
Then "C3" cell was displayed as "0" and "D3" cell was displayed as "1" which is the result of "1+0" calculation, not an error.
Measures to be taken when strings are present in the calculation process
Looking at the input values and calculation results in lines 5 and 6, both "Column C" shows "1", but looking at the formula bar
In cell "C5," though, "1 piece" is entered,
In cell "C6" only "1" is entered.
In other words, line 5 is a string, not a number, which is causing the error.
This one should also be improved, not "column D" but "column C". If you are trying to do calculations in Excel, the string should not be included, but there are many things that would be easier to see if such units were displayed.
In such cases, the input should be numeric only, and the units should be displayed in the formatting.
How to change formatting
Select the cells you want to format, "right-click" on them, and click "Format Cells" from the menu that appears.
From the menu that appears, click on "User Defined" at the bottom of the classification in the "Display Format" tab, enter "0″ pieces" in the input field shown below, and then click the "OK" button.
0 "piece"
Now the "formatting" is complete, but this is still giving the error "#VALUE!
Remove the string "piece" from the input value in cell "C5" to make it numeric only.
This eliminated the "#VALUE!" error.
Conclusion
The "#VALUE!" error occurs when there is a "string" in the calculation process,
- Keep strings out of the calculation process.
- Show comment if string is present
and other actions are required.
Even so, it is possible that for some reason a string may be included. In such cases, it is useful to display a comment so that the user can see that the value is a string rather than a number.
For example, in cell "D5", enter the following
= IFERROR(B5+C5, "The number of pieces is a string. Please correct it to a number.")In this situation, if the "C5" cell is a string.
If you see this, you will notice that something is wrong with the input value of the number of pieces.
These are the meanings and measures of "#VALUE!
Please refer to this error when you get this error.
Comment