Wednesday 20 January 2010 @ 10:35 am
i need to understand the common formula error messages in Excel, such as :
#name
#value
#Num
#ref
#N/A
#DIV/0
i’m a beginner in Excel, please help if you have an idea about these stuff.
thanks
Tags: ICDL 
3 responses to “i have ICDL exam tomorrow in Microsoft Excel?”
#NAME!
Occurs when Microsoft Excel doesn’t recognize text in a formula. Possible causes:
•Using a function that is part of the Analysis Toolpak add-in, without the add-in being loaded
•Using a name that does not exist
•Misspelling the name
•Using a label in a formula, without labels being allowed
•Misspelling the name of a function
•Entering text in a formula without enclosing the text in double quotation marks
•Omitting a colon (:) in a range reference
•Referencing another sheet not enclosed in single quotation marks
#VALUE!
Occurs when the wrong type of argument or operand is used. Possible causes:
•Entering text when the formula requires a number or a logical value, such as TRUE or FALSE
•Entering or editing an array formula, and then pressing ENTER
•Entering a cell reference, a formula, or a function as an array constant
•Supplying a range to an operator or a function that requires a single value, not a range
•Using a matrix that is not valid in one of the matrix worksheet functions
•Running a macro that enters a function that returns #VALUE!
#NUM!
Occurs with invalid numeric values in a formula or function. Possible causes:
•Using an unacceptable argument in a function that requires a numeric argument
•Using a worksheet function that iterates, such as IRR or RATE, and the function cannot find a result
•Entering a formula that produces a number that is too large or too small to be represented in Microsoft Excel
#REF!
Occurs when a cell reference is not valid. Possible causes:
•Deleting cells referred to by other formulas, or pasting moved cells over cells referred to by other formulas
•Using a link to a program that is not running
•Linking to a Dynamic Data Exchange (DDE) topic such as “system” that is not available
•Running a macro that enters a function that returns #REF!
#N/A!
Occurs when a value is not available to a function or formula. Possible causes:
•Missing data, and #N/A or NA() has been entered in its place
•Giving an inappropriate value for the lookup value argument in the HLOOKUP, LOOKUP, MATCH, or VLOOKUP worksheet function
•Using the VLOOKUP, HLOOKUP, or MATCH worksheet function to locate a value in an unsorted table
•Using an argument in an array formula that is not the same number of rows or columns as the range that contains the array formula
•Omitting one or more required arguments from a built-in or custom worksheet function
•Using a custom worksheet function that is not available
•Running a macro that enters a function that returns #N/A
#DIV/0!
Occurs when a number is divided by zero (0). Possible causes:
•Entering a formula that contains explicit division by zero (0) for example, =5/0
•Using the cell reference to a blank cell or to a cell that contains zero as a divisor
•Running a macro that uses a function or a formula that returns #DIV/0!
#NAME! errors are caused when Excel doesn’t recognize a name used in your formula. The best way to fix this is to double-click the cell that has this error, and be sure that you spelled the formula correctly. It is also possible that the formula doesn’t exist. Try clicking the Insert menu and selecting “Function…” from the menu that appears. You will then have access to a library of functions that you can use in Excel.
#VALUE! errors are caused when you perform a mathematical operation on cells that contain text and values.
#REF! errors occur when a spreadsheet formula contains incorrect cell references. To avoid this, u can:
—-use the undo feature to recover lost data.
—-if the data cannot be recovered, re-enter the data and adjust the cell references if needed
—-correct the cell references for formulas that have been copied or moved
—-correct the cell references for formulas when the data has been moved
#N/A! means Not A Number, occurs when a value is not available to a function or formula.
#DIV/0! occurs when a number is divided either by zero (0) or by a cell that contains no value.
Ehh its quite complicated for a beginner.
i know some of them.. i advice you to buy this book call
“Microsoft Excel for dummies”