Troubleshoot error values in a spreadsheet

MS OFfice Web Components Spreadsheet

Troubleshoot error values in a spreadsheet

If a formula cannot properly evaluate a result, the spreadsheet displays an error value. For example, error values can result when a formula expects a numeric value but finds text, when a cell that is referenced by a formula has been deleted, or when the cell is not wide enough to display the result.

Error values might not be caused by the formula itself. For example, if a formula shows #N/A or #VALUE!, a cell referenced by the formula might contain the error.

Following are possible causes and solutions for each error value.

#####

A ##### error value occurs when the cell contains a number, date, or time that is wider than the cell. You can resize the column by dragging the boundary between the column headings.

#CIRC!

The #CIRC! error value occurs when a formula refers to itself, either directly or indirectly.

Possible cause	Suggested action
The formula in the cell refers to itself directly. For example, cell B10 contains the formula =SUM(B5:B10).	Check the references in the formula and make sure the references do not refer to the cell itself.
The formula in the cell refers to itself indirectly. For example, cell B10 contains the formula =SUM(B5:B9) and cell B5 contains the formula =B10*0.5.	Check all cells that are referenced by the formula, and make sure none of them contains a reference to the cell that contains the #CIRC! error. If any of the cells that are referenced by the formula contain formulas themselves, make sure the cells that are referenced by those formulas do not contain references to the cell that contains the #CIRC! error.

#DIV/0!

The #DIV/0! error value occurs when a formula divides by 0 (zero).

Possible cause	Suggested action
Using a divisor that refers to a blank cell or to a cell that contains 0. (If an operand refers to a cell that is blank, it is interpreted as 0.)	Change the cell reference, or enter a value other than 0 in the cell that's used as a divisor. You can enter the value #N/A in the cell that's referenced as the divisor, which changes the result of the formula to #N/A, denoting that the divisor value is not available. To prevent an error value from appearing, use the IF spreadsheet function. If the divisor refers to a cell that's blank or contains 0, the IF function can display nothing instead of an error value. For example, use =IF(B5=0,"",A5/B5). The two quotation marks represent an empty text string.
Using 0 for a divisor — for example, =5/0.	Change the divisor to a number other than 0.

Possible cause

Suggested action

Using a divisor that refers to a blank cell or to a cell that contains 0. (If an operand refers to a cell that is blank, it is interpreted as 0.)

Change the cell reference, or enter a value other than 0 in the cell that's used as a divisor. You can enter the value #N/A in the cell that's referenced as the divisor, which changes the result of the formula to #N/A, denoting that the divisor value is not available.

To prevent an error value from appearing, use the IF spreadsheet function. If the divisor refers to a cell that's blank or contains 0, the IF function can display nothing instead of an error value. For example, use =IF(B5=0,"",A5/B5). The two quotation marks represent an empty text string.

Using 0 for a divisor — for example, =5/0.

Change the divisor to a number other than 0.

#N/A

The #N/A error value occurs when a value is not available to a function or a formula.

If certain cells on your spreadsheet will contain data that is not yet available, you can enter #N/A in those cells so that formulas that refer to those cells will return #N/A instead of attempting to calculate a value.

If the necessary values are available, and you see the #N/A error value where you do not expect it, the function might have specific requirements for arguments that have not been met, or values might not be referenced correctly in a formula or in function arguments.

Possible cause	Suggested action
Giving an inappropriate value for the lookup_value argument in the HLOOKUP, LOOKUP, MATCH, or VLOOKUP spreadsheet function.	Make sure the lookup_value argument is the correct type of value — for example, a value or a cell reference, but not a range reference.
Using the MATCH spreadsheet function with an improper match_type argument.	The MATCH spreadsheet function contains a match_type argument that specifies how the function matches the lookup_value argument with values in the lookup_array argument. For example, the match_type argument can be set to look for a value that is a close match or an equal match in a lookup_array argument that is sorted in ascending or descending order, or in a lookup_array argument that is not sorted. If the function cannot find a match, make sure the lookup_array argument is sorted according to the match_type argument you are using, and/or try to change the match_type argument.
Using the HLOOKUP or VLOOKUP spreadsheet function with the range_lookup argument set to FALSE to locate a value that is not available.	The VLOOKUP and HLOOKUP spreadsheet functions contain a range_lookup argument that tells the function to find an exact match or an approximate match. If the argument is TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than the lookup_value argument will be returned. If the argument is FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned. Make sure the value you are looking for is in the lookup_array argument.
Omitting one or more arguments in a spreadsheet function.	Include all required arguments in the function.

#NAME?

The #NAME? error value occurs when text in a formula is not recognized.

Possible cause	Suggested action
Misspelling the name of a function.	Correct the spelling.
Entering text in a formula without enclosing the text in double quotation marks. The entry is not interpreted properly as text.	Enclose text in the formula in double quotation marks. For example, the following formula joins a piece of text "The total amount is " with the value in cell B50: ="The total amount is "&B50
Omitting a colon (:) in a range reference.	Make sure all range references in the formula use a colon (:). For example, =SUM(A1:C10).

#NULL!

The #NULL! error value occurs when you specify an intersection of two areas that do not, in fact, intersect.

Possible cause	Suggested action
Using an incorrect range operator.	To refer to two areas that don't intersect, use the union operator, the comma (,). For example, the formula =SUM(A1:A10,C1:C10) sums two ranges that do not intersect, with the comma separating the two ranges. To refer to two areas that do intersect, use the intersection operator, the single space. For example, =SUM(B5:C15 A10:D10) calculates the sum of the cells that both ranges have in common — that is, where they intersect — B10 and C10.
Using an incorrect cell reference.	Check for typing errors in the reference to the ranges.

Possible cause

Suggested action

Using an incorrect range operator.

To refer to two areas that don't intersect, use the union operator, the comma (,). For example, the formula =SUM(A1:A10,C1:C10) sums two ranges that do not intersect, with the comma separating the two ranges.

To refer to two areas that do intersect, use the intersection operator, the single space. For example, =SUM(B5:C15 A10:D10) calculates the sum of the cells that both ranges have in common — that is, where they intersect — B10 and C10.

Using an incorrect cell reference.

Check for typing errors in the reference to the ranges.

#NUM!

The #NUM! error value occurs when a problem occurs with a number in a formula or function.

Possible cause	Suggested action
Using an inappropriate argument in a function that requires a numeric argument.	Make sure the arguments that are used in the function are the correct type.
Using a spreadsheet function that iterates, such as IRR or RATE, when the function cannot find a result that's accurate within a certain percent after a certain number of tries.	Try different values for the guess argument.
A formula that produces a number that is too large or too small to be represented in the spreadsheet.	Values in the spreadsheet must be between and .

#REF!

The #REF! error value occurs when a cell reference is not valid.

Possible cause	Suggested action
Deleting cells that are referred to by other formulas, or pasting moved cells over cells that are referred to by other formulas.	Correct the formulas, or restore the cells on the spreadsheet by clicking Undo immediately after you delete or paste the cells.

#VALUE!

The #VALUE! error value occurs when the wrong type of argument or operand is used.

Possible cause	Suggested action
Entering text when the formula requires a number or a logical value, such as TRUE or FALSE. The text cannot be translated into the correct data type.	Make sure that the value for the operand or argument is of the required data type for the formula or function and that the cells that are referenced by the formula contain valid values. For example, if cell A5 contains a number and cell A6 contains the text "Not available", the formula =A5+A6 will return the error #VALUE!. Use the SUM spreadsheet function in the formula as follows to add the two values (the SUM function ignores text): =SUM(A5:A6)
Supplying a range to an operator or a function that requires a single value.	Change the range to a single value.
Omitting a range when a function requires one.	Some functions, such as COUNTIF and SUMIF, require a range. Make sure you have included all required range arguments.
Supplying a value other than 1 for the A1 argument of the ADDRESS spreadsheet function.	The ADDRESS function can return only A1 style references. Change the argument to 1.
Using computed criteria — a formula as a condition — for the database functions (Dfunctions).	The database functions in the Spreadsheet Component do not support computed criteria (for example, "=Field1<Field2"). Change the criteria to simple conditions such as "Field1".
Referring to multiple ranges with the AREAS spreadsheet function.	The AREAS spreadsheet function does not support multiple ranges. Refer to only one range of cells.

previous page start next page