I mentioned earlier that one very common error code in spreadsheets is what I’ll call the “name error”. In this post I’ll cover the definition of the error, what it looks like in various spreadsheet programs, and some simple techniques for fixing it.
In technical terms, this error occurs whenever you tell a spreadsheet to use a name that you haven’t already defined. That said, there’s really two ways to get this error. I suppose you could call them sins of omission, and sins of commission.
The “omission” type is like asking a five-year-old for the budget. It’s never been taught what a budget is, so has no way to know what you’re asking for. That’s like typing “=budget”; when you haven’t yet defined the name. You “omitted” defining the name before asking for it.
The “commission” type is slightly more subtle. Imagine writing an email to someone and asking for the “bdget”. There would really be no need to write a second email saying “whoops, I meant budget” because you’re working with someone sentient. Assuming they speak English reasonably well they can figure out what you meant. Computers, however, are NOT sentient. Therefore if you “commit” a typo and enter “=bdget” into a spreadsheet it’ll be as confused as a human would be if you asked them for the “g7et35” (that’s “budget” if you’re a touch-typist with your fingers a row above where they’re supposed to be.)
In Excel 97 and 2007, and in Google Spreadsheet and OpenOffice Calc, the error code that appears is #NAME? This error code appears in the cell, where you would normally see the result. You can’t see it in the formula bar, and it doesn’t tell you which bit of the formula is causing the problem.
In Microsoft Works Spreadsheet you get a popup window that says, “The formula contains an error. Click OK to return to the formula and correct it.”
So, now that you’ve got a “name error”, and you know what it means, how do you fix it? The steps below will allow you to identify the problem.
- Look at the formula. You can see it by either looking at the formula bar or by clicking inside the cell itself.
- Make a list of all the names you used. If there’s only a few, then a mental list is fine. Otherwise it might be good to make a copy of the formula and paste it into a word processor, then delete everything that isn’t a name and put everything that IS a name on its own line. Remember that if you typo’d “b2” as “b” that counts as a name! So does using an x instead of a * for multiplication.
- Look up each name in the list in the list of defined names. If you find any that aren’t defined, there are two possibilities. Possibility one is that you just hadn’t defined it yet, in which case you simply need to define it. Possibility two is that you had defined it under a different name, such as “length” vs “pages” when talking about books. In this case you need to decide whether to correct the name in the formula or define a second name for the same set of cells.
- If every name in your list is defined, double-check the spelling of each one. Did you type “bdget” instead of “budget”, or “titles” instead of “title”? This is part of why it’s good to keep the names as short as possible while still being sensible, even if the program allows you to make them infinitely long. After all, do you want to try to spot the typo in “supercalifragilisticexpialidocious”?