exceltoxl.com

Hi there,

Share on Facebook

Today we will attempt to learn how to crack formula errors. This is our day-to-day challenge to our trifling pint-sized brains. Everyone who works on formulas will definitely come across formula errors atleast once in an hour, if you are a hard-core formula writer,.  When I was working (still learning) on formulas I used to get hell lot of errors and to debug those will absolutely take not less than minutes. If the formula goes bigger then to find the cause of an error is like having an ice cream on a rainy day. Finally, we will finish our ice cream before debugging a formula :). Here are few choices for you to choose. Please take one at a time :).  

I think everyone is done with your ice creams right. Now, we will proceed with the subject :).

Formula Errors:

Formula errors are of 8 types and those are listed as below. 

1. #NAME?
2. #N/A
3. #NUM!
4. #DIV/0!
5. #NULL
6. REF!
7. ######
8. VALUE!

...

#NAME?:  This error is commonly received by the one who refers 'User Defined Functions' instead of cell ranges in the formulas. This is a good idea to work with names rather using cell ranges. However, we should make sure using the correct names. In 2007 and above versions we can avoid this error. When we type 'isequalto' in a cell and first letter of a keyword, here it is 'R', then the list of functions appear starting with 'R'. We can select the appropriate function and press TAB above 'caps lock' key in the normal keyboards will select the complete function rather than writing it. In the below screen we see 'Range' as a name assigned for the range A1:A5. 

Simple example: In cell A1 type "=I am learning excel formulas from exceltoxl.com" press enter.

REASONS:

• Names are wrongly/incorrectly spelled in a formula or cell.
• Using a name that does not exists.
• Using name in a formula without keeping it in double quotes.

#N/A: This is a common error for who uses lookup functions. We receive this error while we use Vlookup, Hlookup, Lookup and etc., in the given picture, cell A1 is empty, as the referred cell is empty the formula shows #N/A error.

REASONS: 

• If the cell referred by the user was deleted.
• If the user tries to refer non-existing cell.
• If the value of the cell is empty.

#NUM!: This error type is very rare unless few circumstances like below. This can be avoided using small or proper numeric values. In other words entering a non-numeric argument to a function cause this error. The below picture says cell D2 has the bigger number  which looks like 5.3E+21 however the same number showing in formula bar or D4 is different than D2 and the number displaying in D4 or formula bar is again different than what I have manually entered (which is not there in the pic). In cell G2 the #NUM! error is showing because I have powered the value of D2 to the power of 254856.

REASONS: 

• If the cell value is bigger than excel can perform after many iterations.
• If the value of the cell is non-numeric like %, $ etc.,
• Example for bigger value appears in the cell 5.3E+21
   

#DIV/0!: This error is commonly occur when we divide a value by zero. For eg: 143/0 should actually give I LOVE YOU but here it gives #DIV/0 error. This might be due to rejection of love. ;)

REASON:

• If the value of a cell is divided by zero.

REF!: This error is commonly seen in day-to-day while working on dashboards, scorecards, financial reporting's etc., . This is basically due to an invalid range which means the selected range might have deleted by users if the workbook is shared or the data is overwritten by the user(s). Either cell, column, row, worksheet or workbook deletion will have this error. Usually before deletion one must make sure if the formulas in the worksheet or workbook is referred to any other worksheets or workbooks. 

example: 

######: This is a typical error type. We see this when the cell content is more than the space of the cell otherwise insufficient column width. To avoid we can reset the width of the cell to its content. 

VALUE! : This is a rarely found error type. This error occurs atleast once in a day if the user is hard-core formula writer. In the below picture we can see the named range is applied to 'if' function incorrectly. The reasons for this are:

REASONS: 

• If the user specifies wrong arguments to a function. 
• Arguments includes cell references, named ranges, functions etc.,
• If the user specifies incorrect text parameters to a function which accepts numbers.

BEST SOLUTION:

The best solution to avoid reflecting these errors in the data that we submit to the management is using "iferror" function. We can use Iferror to replace most of the above errors by " " (using space), "-" (using hyphen) etc.,

Debugging formula errors is the most and best common practice for Excel users. The best way to avoid the most common errors are by doing R&D.

WHAT IS YOUR BEST PRACTICE:

Do you find this post is informative then please share your ideas and comments on this post. If you have different experiences or opinions on the formula errors please use this space to share your comments in the comments section. This will make every one aware of your experiences while working on formulas and its errors. Let the blog members also learn and enjoy the new outcomes. 

If you like this post then please subscribe to exceltoxl.com and share the details to your kind co-hearts.

Thank you and wishing you all the very luck.