Hello and welcome to A1 Excel! If you've ever had the experience of working diligently on a formula only to have it result in the following: #DIV/0!, #N/A, #Name?, #NULL!, #NUM!, #REF!, or #VALUE! then this post is for you. Here I will be going over each of the errors listed above and how to correct them.
Let's get started:
#DIV/0!: This error shows when a number is divided by zero. Like in the following table of examples:
To correct this error code you need to change the cell reference so it won't be dividing by zero in the formulas, confirm the formula is correct, or enter "N/A" to indicate the divisor is not available/applicable. A way to have it show as "N/A" automatically is to include it in an IF statement. This will work as a placeholder (this works especially well in a model where percentages are utilized) if you are awaiting additional data to help make the divisor a nonzero. Please see examples below:
#N/A: This error typically means that a formula can't find what it's been tasked to look for. This is most commonly experienced VLOOKUP, XLOOKUP, HLOOKUP, or other types of lookup functions.
Similar to the solution in the #DIV/0 error code we will tack on an IF statement to adjust for the error. This error only exists here because there is no pet name listed under "Dog" in the Lookup Table.
#NAME?: The top reason why this error code pops up is because there's a typo in the formula or other syntax.
Here's an example based on the formulas utilized in the #N/A error example:
As you can see the error is because I typed in an additional "I" in the IFERROR statement. Once I remove the additional "I" then the result will show correctly:
#NULL!: This error shows when an incorrect range operator in a formula or when you use an intersection operator (like a space) between references. If this happens simply double check the cell reference range. In my own experiences this happens from typing a range too fast or not having an accurate range selection.
#NUM!: This error shows when a formula or function contains numerical values that are not valid. This can happen, for example, if you've entered a data type or number format that is not consistent. To correct this ensure that your data is the same data type/format.
#REF!: This error shows when a formula refers to a cell that is no longer valid. This happens mainly when cells that were previously referenced have been deleted so be mindful of this when formatting data summary tables or anything else of that nature.
#VALUE!: This error shows when Excel sees something wrong with how you typed the formula or if there something wrong with the cells referenced. The error is very general though and it can be difficult at times to find the exact cause of it. I would recommend investigating the data to determine if there's inconsistencies that Excel may find inconsistent or retyping your formula and double check for accuracy.
Anyways, I hope this post has been helpful!
Yorumlar