Excel formulas and functions - Tips for Troubleshooting

Finding & fixing errors in excel formulas & functions can be tedious & time-consuming.  Here are some practical tips to help you cut to the chase.

Error values: Not a nuisance but are clues

Excel displays an error value, if it cannot calculate a formula. These values are important clues & knowing what they mean can help quickly spot errors. The following error values are your initial steps, & often the only steps, to debug the error:

1. #VALUE!: You've expressed the wrong data type or operator. The most common cause for this error value is a reference to text by a numeric operation. 
2. #REF!: You've referenced a cell incorrectly. Most likely, you've deleted a cell. For instance, the expression =A1+B1 won't return an error if you delete the value in B1. But if you delete column B, it will. 
3. #NUM!: This error value indicates a problem with a number in the formula. Either the argument is invalid or the result is too large or small. 
4. #NULL!: You specified an intersection between two ranges that don't intersect, such as A1:C3 & D4:F6. If the ranges do intersect, you've probably used a space character (the union operator) instead of a comma to separate the two ranges (A1:C3 D4:F6 instead of A1:C3,D4:F6). 
5. #NAME?: This error occurs when you refer to a range incorrectly or forget to enclose text in quotation marks. When Excel encounters text in a formula, it tries to interpret the text as a cell reference, a range name, or a function name. When it can't recognize the text as any of those things, it displays this error value. Check your function names, cell references, & range names (make sure the range name actually exists if you've spelled it correctly). If all ranges are correct, make sure you've delimited text using quotation marks. 
6. #DIV/0: Excel can't divide by 0 & your formula refers to a cell that contains or evaluates to 0 or is blank. This is one of the easiest to find & fix.

Happy Learning!!