Today's post is brought to you by one of my instructor's by way of his textbook. In it my professor is outlining different functions within Excel and errors you may come across. This is something I felt worth sharing.
Common Formula Errors
A number of common mistakes are made by novice and expert users alike when using formulas in spreadsheets. The following errors can appear in Excel spreadsheets; similar errors are reported in other packages:
• #NAME—This is one of the most common errors. It means that Excel cannot find the function you are trying to use. What most likely happened is that you misspelled the function name. Maybe you typed CONUTIF instead of COUNTIF. Or you have a syntax error somewhere else. Are you missing quotes? A comma? A colon? (Better get that checked out.) Another reason this can show up is if you are referring to an advanced function that is not loaded in your version of Excel. This happens with older versions of Excel and functions from Add-In packs.
• #DIV/0—Despite your desires, Excel will not divide by zero. Somewhere you have two cells dividing (sounds like Biology), and one of them can be a zero. The quickest way to sniff this one out is to look for division symbols and then find the denominator cells. Once you have done this, try to figure out under what conditions those can be zeros.
• #VALUE—Something is wrong with your arguments. Maybe a function expects a logical operation, and you used a string. Or it expects numbers, and you used strings. Or perhaps you left out a required argument in a function.
• #NUM—This is usually an easy fix. A formula expects a number, but it is getting text that is not a number. Or you are returning a number that is larger than what Excel can handle. If you are using a function like IRR that guesses and checks values recursively until it finds a solution, you’ll get this error if the function goes for too long without finding an answer. To fix this particular problem, check to make sure your problem has an answer, and assuming it does, provide a better starting guess.
• #REF—This one is annoying. Excel is trying to find a cell that does not exist.
• #N/A—This one is the most annoying of all because it’s a “catch-all” error message that does not narrow down a cause. It’s likely you are using a function in the wrong way, supplying bad arguments, or violating some assumption of the function (such as how VLOOKUP assumes the lookup column is the leftmost column.) It’s also returned when you are using a searching function that cannot zero in on a value.
• ########—If you see a cell full of pound symbols, don’t despair! This just means that your cell is not wide enough to display the proper value. This is not actually an error at all. Just drag the cell width larger, and you should eventually see the correct value.
- "Players Making Decisions: Game Design Essentials and the Art of Understanding Your Players" - Zack Hiwiller