- Image and text skills
During the table calculation process, if there is an error in the function or calculation formula, it will cause a calculation error and the error value #VALUE will be displayed!
Common reasons for formula errors include incorrect formula syntax, empty character cells in quoted formulas, text cells in calculations, incorrect formats used in array calculations, etc.
This issue will explain to you the main reasons and solutions for this error value.
Cause 1: The formula syntax is incorrect
▪ If we want to calculate A2+B2-C2-D2, enter the formula =SUM(A2: B2-C2-D2) yields the wrong value #VALUE!
▪This is because A2:B2 is a continuous area reference and is calculated in cells that do not intersect with When, you need to add comma separation. When the formula syntax is incorrect, it will cause the error value #VALUE!
Just change it to =SUM(A2:B2,-C2-D2) at this time.
Reason 2: Use operators to calculate cells with text
▪We use tables to sum cell operations, and sometimes error values #VALUE appear!
Take this table as an example, enter =C3+C4+C5+C6+C7 in cell C8, press Enter to confirm, and the result displays the error value #VALUE!
▪Encountering this situation is because the cells with text, such as text, words, special symbols, etc., are calculated using the operator plus sign.
Use the table positioning function, CTRL+G to quickly locate cells with text, and modify the contents of cells with text to numbers so that they can be calculated correctly.
Cause three: The cell referenced by the formula contains an empty string
▪When we use calculations in the table, we reference cells with empty strings. Can lead to calculation errors.
For example, we want to calculate the number of days between the start and end dates of this table.
Enter =DATEDIF(B3,C3,d) in cell D3, pull down to fill in the formula, and the result shows the wrong value #VALUE!
▪If you encounter this situation, it may be because the referenced date cell contains an empty string.
At this time, clear the empty strings before and after the date cell to get the correct calculation result.
Use the find and replace function of WPS, Ctrl+H to quickly find the empty string in the cell, and replace and clear it with one click.
Cause 4: Correctly formatted braces are not used in array calculations
▪When we do array calculations in tables, not using the correct format of braces will also Resulting in the error value #VALUE!
For example, when using the TRANSPOSE function to transpose a table, the incorrect value #VALUE! sometimes appears
▪This is because in this array formula, you need to use the Ctrl+Shift+Enter shortcut key to enclose the formula in curly brackets.
The above is #VALUE! Do you understand the common causes of errors?
Articles are uploaded by users and are for non-commercial browsing only. Posted by: Lomu, please indicate the source: https://www.daogebangong.com/en/articles/detail/biao-ge-cuo-wu-zhi-xi-lie-ke-cheng-cuo-wu-zhi-VALUE-chang-jian-chu-cuo-yuan-yin-yu-jie-jue-fang-fa.html
评论列表(196条)
测试