Making of #N/A Error & Solve it
If You are an Frequent MS Excel User, then you should have seen those Unexpected return codes from Formula that start with Hash(#). These are the Error Codes that those formula return when it could not process the Input Data or Invalid Data.This Article explains about generating such return codes on our own for testing purpose and learn a bit about how to fix them.
In this OfficeTricks Article, it is already explained how to remove #N/A or #Value that occur in many Excel Formula. But here, we are going to see how to simulate an such return value in case we need to test any fix.
We might have devised a wonderful logic to avoid an error occurrence. But, after coding it, we have to hit Google to learn how to generate an error condition to test our code. Here, We have explained different ways to simulate then test your own code & confirm that your logic solves the issue.
Different Types of #N/A Error in Excel
With Excel in hand everything is do-able within in a Windows System. It provides an easy function ‘NA’ that always returns ‘#N/A’ Value. Open an Excel sheet and type the below function in any cell. Then hit enter to get the error return code ‘#N/A’. In case you want to generate this return value, using a Lookup function, use one of the below example code.
=Vlookup(1,1,1,1)
=Lookup(“0”,0)
All the above functions return the same #N/A Return Value. Now we know how to create this code. Let’s extend this knowledge a bit more about how to generate other possible faulty return code in Excel. Use below listed formula in excel to get the corresponding Error codes mentioned side-by-side.
=VLOOKUP(1,”a”,1,1) gives #VALUE!
=VLOOKUP(a,1,1,1)gives #NAME
Converting A Error Value Into Numeric
In case if we don’t want to see these error codes as a result from excel formula and just want to see numbers instead of Error values, then use ‘Error.Type(Error Value)’ . This function will return a numeric code for each error value. This way we don’t have to see any junk characters in the return values of any Excel Formula.
Now we know how to create a few major erroneous return codes in Excel. Now it is all set to use this method in our testing. In case if you are here to know how to remove an #N/A or any error for that matters, use one of these formula – IFERROR, ISERR, ISERROR, ISNA.
To know more details on how to use these functions inside a nested Excel Formula, refer the link mentioned at the beginning of this article, that explains more about handling Excel Formula Errors.