In anticipation of next weeks “Back to Basics” course we a running a series of articles on when excel doesn’t behave in the way that we expect it to.
Starting with the inevitable “Error” message.
Ever had a time when you are working on a spreadsheet, filling down a row of formulas and instead of getting the result you were expecting, you get gobblegook. A message may be displayed but what does that mean?
Sometimes it’s just a series of hashes which is simply saying that the column is too thin to display the result.
This can be fixed in a number of ways by grabbing the top of the header and stretching it till the result appears, going to format> columns > autofit (which will do it all for you), or going to number format and changing the style (i.e. long date to short date).
This is just a space to display issue. But other times you will a N/A error or value, which means that there is either something wrong with the formula, or the cells that it is referring to. The annoying thing about that is that if it only affects some cells in that column, then any calculation done over that range will also throw up an error.
All is not lost as the nice thing about later versions of excel is that it produces an “indicator” on the right hand side which goes to a link which describes possible causes. Right click on the mouse and extra options are available to you.
The following is a list of errors that excel can produce:
#DIV/0! Trying to divide the number by 0.
This is because the Mathematics won’t work
#N/A! A formula is refereeing to a cell within its own range argument or to itself.
In this case it is looking up something within its own range. B2 to B2.
#NAME? The formula has a spelling mistake.
(i.e. VOOKUP rather that VLOOKUP)
#NULL! A space was used in a formula that references multiple ranges
In this case there is no separator between the ranges in column B and C.
#NUM! A formula returns a value that excel cannot display.
In this case the iterations between decimal points.
#REF! The formula cannot find the cell.
In this case “Sheet 2” has been deleted so the cell it refers to is no longer in existence.
#VALUE! The wrong formula is being used.
In this case the formula is trying to divide from a text field.
You can go into the options > formulas and adapt these rules but then you still won’t know what the problem is to fix the issue and end up with inconsistent results.
Ultimately these are “Syntax” errors generated by the user and excel provides some internal guidelines on how to deal with them. Ultimately when you begin inputting formulas into excel you are in fact programing, which means you need to abide by the logic which the program needs in order to operate in the manner the user expects.
Next: How to use excel errors to your advantage.