BET: Microsoft Excel Formulas

Leading the way in Microsoft Office Development

Home

Excel

Word

Microsoft Excel > Formulas > Formula Errors

< Previous | Next >

Step 1 - Finding any Formula Errors

	Sometimes when you enter a formula an error will occur. This is to indicate that the formula syntax is incorrect.
	If this error occurs press OK to be taken back to the formula bar. You can either correct the formula or press ESC to remove the formula completely.
	This error may be caused by missing parentheses or incorrect arguments being passed to functions (e.g. passing a string when it is expecting a number).
	To quickly locate any cells that contain errors, select (Edit > GoTo > Special) and tick the Formulas, Errors checkbox.

Step 2 - Different Types of Errors

	The following errors can be returned from your formulas.
	Even formula that have the correct syntax can occasionally return error values.
	The following is a list of the possible error values and the reasons the error might have be generated.

######	This is displayed when a column is not wide enough to display the result. This is not technically an error.
	Using a negative date or time.
	A ##### error value occurs when the cell contains a number, date, or time that is wider than the cell or when the cell contains a date or time formula that produces a negative result. Try increasing the width of the column.
#DIV/0!	Dividing a number by zero.
	Dividing a number by the contents of an empty cell. (xlErrDiv)
#N/A	Using VLOOKUP, HLOOKUP or MATCH functions when they do not return a match (or the list is not sorted).
	Using a custom worksheet function that is not available.
	Using a worksheet function without submitting all the required parameters.
	You have used the NA() worksheet function. (xlErrNA)
#NAME?	Referring to a named range that does not exist.
	Using a worksheet function that does not exist (or has been spelt incorrectly).
	Using labels when labels are not allowed.
	Entering text that is not enclosed in double speech marks ("some text").
	Using an invalid cell range reference.
	Referring to another worksheet that does not exist. (xlErrName)
#NULL!	Using an incorrect range separator.
	Finding the intersection of two cell ranges that do not intersect. (xlErrNull)
#NUM!	Passing the incorrect argument to a worksheet function.
	Using a function that iterates (e.g. IRR or RATE) and no result can be found.
	The number returned is too big or too small for Excel to recognise it. (xlErrNum)
#REF!	Referring to cells that do not contain any data.
	Referring to cells that may have been deleted.
	Using a Dynamic Data Exchange Link that is not available. (xlErrRef)
#VALUE!	The formula contains a parameter of the wrong datatype, i.e. the formula is expecting an integer but you have passed in some text. (xlErrValue)

Step 3 - Trying to Preventing Errors

A common method used to try and eliminate errors from appears on your worksheet is to use the ISERROR() worksheet function as a wrapper.

	The formula is cell B2 tries to divide D2 by D3, which generates an error as division by zero is not possible.
	The formula is cell B3 includes the ISERROR() function as a wrapper around the formula.

Step 4 - Generating Errors for Testing

The following table shows you examples of formulas that will generate the necessary error values.

Step 5 - Things to Remember

		To prevent the misspelling of named ranges select the Name Box to insert them into your formulas.
		The AutoCorrect feature will often eliminate some of the more common formula entry errors.

< Previous | Top | Next >

Leading the way in Microsoft Office Development

Step 1 - Finding any Formula Errors

Sometimes when you enter a formula an error will occur. This is to indicate that the formula syntax is incorrect.

If this error occurs press OK to be taken back to the formula bar. You can either correct the formula or press ESC to remove the formula completely.

This error may be caused by missing parentheses or incorrect arguments being passed to functions (e.g. passing a string when it is expecting a number).

To quickly locate any cells that contain errors, select (Edit > GoTo > Special) and tick the Formulas, Errors checkbox.

Step 2 - Different Types of Errors

The following errors can be returned from your formulas.

Even formula that have the correct syntax can occasionally return error values.

The following is a list of the possible error values and the reasons the error might have be generated.

Step 3 - Trying to Preventing Errors

A common method used to try and eliminate errors from appears on your worksheet is to use the ISERROR() worksheet function as a wrapper.

The formula is cell B2 tries to divide D2 by D3, which generates an error as division by zero is not possible.

The formula is cell B3 includes the ISERROR() function as a wrapper around the formula.

Step 4 - Generating Errors for Testing

The following table shows you examples of formulas that will generate the necessary error values.

Step 5 - Things to Remember

To prevent the misspelling of named ranges select the Name Box to insert them into your formulas.

The AutoCorrect feature will often eliminate some of the more common formula entry errors.