- Image and text skills
#N/A is a common table error value.
Common reasons for errors include search values that do not exist in the search area, Data type mismatch,Find data source reference error , referencing functions or formulas with a return value of #N/A, etc..
This issue will explain to you the main causes and solutions of this error value.
Reason one: The search value does not exist in the search areawill cause#N/A error
▪#N/A error usually means that the formula cannot find what it is looking for .
For example, query the number of "computers",Enter formula=VLOOKUP(G4,$B$1:$D$6 ,3,0),But "computer" does not exist in the data source< /span>, So return #N/A.
When we calculate the sum, the formula contains an error return value #N/A, this will cause the final calculation result to be incorrect.
In order to avoid this situation, you can use theIFERROR function , replaces the error value with text or numeric information.
Enter formula=IFERROR(VLOOKUP(G4,$ B$1:$D$6,3,0),0).
It means that when the formula VLOOKUP(G4,$B$1:$D$6,3,0 ) returns an error value, the specified input value of 0 is returned.
In this way, the error value of H4 can be converted to The value is 0 to avoid subsequent calculation errors.
Reason 2:< /span>Find data source reference errors
▪Taking this table as an example, we can see that the search for the first few items in the table has been completed, and the error value #N/A appears when the cells are dropped down to fill in.
This is becauseWhen querying "refrigerator", the data source selection is B1:D6, FormulaPaddingAfter copying,< span style=" color: rgb(34, 34, 38); letter-spacing: 0px; font-size: 16px;">The data source became B4:D9 due to relative reference, Resulting in no results for "table" query.
▪In this case, the data range needs to be referenced absolutely.
Select the data area and use the F4 key to quickly add an absolute reference to this area, so that the #N/A error can be fixed when you pull down to fill in the formula again. value.
Reason three: Caused by data type mismatch span>#N/AError
▪This may be becauseCaused by different cell formats.
Column Ais in text format,The serial number in column G is in numerical form,When performing a search, the search value must be completely consistent with the data type of the data source object.
▪In this case, just convert the text-formatted cells into numbers.
Reason 4:< strong>The reference return value is #N/AWrong function or formula
▪Take this data table as an example. We can see that the cells contain formula functions that return#N/A error values.
If we want to calculate the sum of the values in row 6, we can see that the calculation result is an #N/A error value.
This is because the return value is#N/A error value formula used in the operation.
▪In this case, we can use theIFERROR function.
Correct the final calculation result by modifying the cell whose return value is #N/A.
The above is the error value #N/ADo you understand the common causes of errors< /span>?
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-N-A-chang-jian-chu-cuo-yuan-yin-yu-jie-jue-fang-fa.html
支付宝扫一扫 
评论列表（196条）
测试