Formula Problems and Solutions

Formula errors tend to fall into one of the following general categories:

• Syntax errors: You have a problem with the syntax of a formula. For example, a formula may have mismatched parentheses, or a function may not have the correct number of arguments.

• Logical errors: A formula does not return an error, but it contains a logical flaw that causes it to return an incorrect result.

• Incorrect reference errors: The logic of the formula is correct, but the formula uses an incorrect cell reference. As a simple example, the range reference in a SUM formula may not include all the data that you want to sum.

• Semantic errors. An example of a semantic error is a function name that is spelled incorrectly. Excel attempts to interpret the misspelled function as a name and displays the #NAME? error.

• Circular references: A circular reference occurs when a formula refers to its own cell, either directly or indirectly. Circular references are useful in a few cases, but most of the time, a circular reference indicates a problem.

• Array formula entry error: When entering (or editing) an array formula, you must press Ctrl+Shft+Enter to enter the formula. If you fail to do so, Excel does not recognize the formula as an array formula. The formula may return an error (if you're lucky) or an incorrect result (if you're not lucky).

• Incomplete calculation errors: The formulas simply aren't calculated fully. Microsoft has acknowledged problems with Excel's calculation engine in some versions of Excel. To ensure that your formulas are fully calculated, press Ctrl+Alt+F9.

Syntax errors are usually the easiest to identify and correct. In most cases, you will know when your formula contains a syntax error. For example, Excel won't permit you to enter a formula with mismatched parentheses. Other syntax errors also usually result in an error display in the cell.

The remainder of this section describes some common formula problems and offers advice on identifying and correcting them.

Mismatched Parentheses

In a formula, every left parenthesis must have a corresponding right parenthesis. If your formula has mismatched parentheses, Excel usually won't permit you to enter it. An exception to this rule involves a simple formula that uses a function. For example, if you enter the following formula (which is missing a closing parenthesis), Excel accepts the formula and provides the missing parenthesis:

 =SUM(A1:A500 

A formula may have an equal number of left and right parentheses, but the parentheses may not match properly. For example, consider the following formula, which converts a text string such that the first character is uppercase and the remaining characters are lowercase. This formula has five pairs of parentheses, and they match properly.

 =UPPER(LEFT(A1))&RIGHT(LOWER(A1),LEN(A1)-1) 

The following formula also has five pairs of parentheses, but they are mismatched. The result displays a syntactically correct formula that simply returns the wrong result.

 =UPPER(LEFT(A1)&RIGHT(LOWER(A1),LEN(A1)-1)) 

Often, parentheses that are in the wrong location will result in a syntax error, which is usually a message that tells you that you entered too many or too few arguments for a function.

Tip 

Excel can help you out with mismatched parentheses. When you edit a formula, use the arrow keys to move the cursor to a parenthesis and pause. Excel displays it (and its matching parenthesis) in bold for about one second. In addition, nested parentheses appear in a different color.

Using Formula AutoCorrect

When you enter a formula that has a syntax error, Excel attempts to determine the problem and offers a suggested correction. The accompanying figure shows an example of a proposed correction.

Exercise caution when accepting corrections for your formulas from Excel because it does not always guess correctly. For example, I entered the following formula (which has mismatched parentheses):

 =AVERAGE(SUM(A1:A12,SUM(B1:B12)) 

Excel then proposed the following correction to the formula:

 =AVERAGE(SUM(A1:A12,SUM(B1:B12))) 

You may be tempted to accept the suggestion without even thinking. In this case, the proposed formula is syntactically correct-but not what I intended. The correct formula is as follows:

 =AVERAGE(SUM(A1:A12),SUM(B1:B12)) 

Cells Are Filled with Hash Marks

A cell displays a series of hash marks (#) for one of two reasons:

• The column is not wide enough to accommodate the formatted numeric value. To correct it, you can make the column wider or use a different number format.

• The cell contains a formula that returns an invalid date or time. For example, Excel does not support dates prior to 1900 or the use of negative time values. Attempting to display either of these will result in a cell filled with hash marks. Widening the column won't fix it.

Blank Cells Are Not Blank

Some Excel users have discovered that by pressing the spacebar, the contents of a cell seem to erase. Actually, pressing the spacebar inserts an invisible space character, which is not the same as erasing the cell.

For example, the following formula returns the number of nonempty cells in range A1:A10. If you "erase" any of these cells by using the spacebar, these cells are included in the count, and the formula returns an incorrect result:

 =COUNTA(A1:A10) 

If your formula does not ignore blank cells the way that it should, check to make sure that the blank cells are really blank cells. One way to do this is to use the Go To Special dialog box. (Choose Home Editing Find & Select Go To Special.) If you choose the Blanks option in the Go To Special dialog box, Excel selects all blank cells so you can spot cells that appear to be empty but are not.

Extra Space Characters

If you have formulas that rely on comparing text, be careful that your text doesn't contain additional space characters. Adding an extra space character is particularly common when data has been imported from another source.

Excel automatically removes trailing spaces from values that you enter, but trailing spaces in text entries are not deleted. It's impossible to tell just by looking at a cell whether text contains one or more trailing space characters.

Formulas Returning an Error

A formula may return any of the following error values:

• #DIV/0!

• #N/A

• #NAME?

• #NULL!

• #NUM!

• #REF!

• #VALUE!

The following sections summarize possible problems that may cause these errors.

Tip 

Excel allows you to choose how error values are printed. To access this feature, choose Page Layout Page Setup Print Titles. In the Sheet tab of the Page Setup dialog box, you can choose to print cell error values as displayed (the default), or as blank cells, dashes, or #N/A.

Tracing Error Values

Often, an error in one cell is the result of an error in a precedent cell (a cell that is used by the formula). To help track down the source of an error value in a cell, select the cell and choose Formulas Formula Auditing Error Checking Trace Error. Excel draws arrows to indicate the error source.

After you identify the error, use Formulas Formula Auditing Error Checking Remove Errors to get rid of the arrow display.

#DIV/0! ERRORS

Division by zero is not a valid operation. If you create a formula that attempts to divide by zero, Excel displays its familiar #DIV/0! error value.

Because Excel considers a blank cell to be zero, you also get this error if your formula divides by a missing value. This problem is common when you create formulas for data that you haven't entered yet, as shown in Figure 21-1. The formula in cell D2, which was copied to the cells below it, is as follows:

 =(C2-B2)/C2 

Figure 21-1: #DIV/0! errors occur when the data in column C is missing.

This formula calculates the percent change between the values in columns B and C. Data is not available for months beyond May, so the formula returns a #DIV/0! error.

To avoid the error display, you can use an IF function to check for a blank cell in column C:

 =IF(C2=0,"",(C2-B2)/C2) 

This formula displays an empty string if cell C2 is blank or contains 0; otherwise, it displays the calculated value.

Another approach is to use the new IFERROR function to check for any error condition. The following formula, for example, displays an empty string if the formula results in any type of error:

 =IFERROR((C2-B2)/C2,"") 

For versions prior to Excel 2007, use this formula.

 =IF(ISERROR((C2-B2)/C2),"",(C2-B2)/C2) 

#N/A ERRORS

The #N/A error occurs if any cell referenced by a formula displays #N/A.

Note 

Some users like to enter =NA( ) or #N/A explicitly for missing data (that is, Not Available). This method makes it perfectly clear that the data is not available and hasn't been deleted accidentally.

The #N/A error also occurs when a lookup function (HLOOKUP, LOOKUP, MATCH, or VLOOKUP) can't find a match.

#NAME? ERRORS

The #NAME? error occurs under these conditions:

• The formula contains an undefined range or cell name.

• The formula contains text that Excel interprets as an undefined name. A misspelled function name, for example, generates a #NAME? error.

• The formula uses a worksheet function that's defined in an add-in, and the add-in is not installed.

Note 

Excel has a bit of a problem with range names. If you delete a name for a cell or range and the name is used in a formula, the formula continues to use the name even though it's no longer defined. As a result, the formula displays #NAME?. You may expect Excel to automatically convert the names to their corresponding cell references, but this does not happen. In fact, Excel does not even provide a way to convert the names used in a formula to the equivalent cell references!

#NULL! ERRORS

The #NULL! error occurs when a formula attempts to use the intersection of two ranges that don't actually intersect. Excel's intersection operator is a space. The following formula, for example, returns #NULL! because the two ranges have no cells in common:

 =SUM(B5:B14 A16:F16) 

The following formula does not return #NULL! but instead displays the contents of cell B9-which represents the intersection of the two ranges.

 =SUM(B5:B14 A9:F9) 

#NUM! ERRORS

A formula returns a #NUM! error if any of the following occurs:

• You pass a non-numeric argument to a function when a numeric argument is expected.

• You pass an invalid argument to a function. For example, this formula returns #NUM!:

   =SQRT(-1) 

• A function that uses iteration can't calculate a result. Examples of functions that use iteration are IRR and RATE.

• A formula returns a value that is too large or too small. Excel supports values between −1E-307 and 1E+307.

#REF! ERRORS

The #REF! error occurs when a formula uses an invalid cell reference. This error can occur in the following situations:

• You delete a cell that is referenced by the formula. For example, the following formula displays a #REF! error if row 1, column A, or column B is deleted.

   =A1/B1 

• You copy a formula to a location that invalidates the relative cell references. For example, if you copy the following formula from cell A2 to cell A1, the formula returns #REF! because it attempts to refer to a nonexistent cell.

   =A1-1 

• You cut a cell (using Home Clipboard Cut, or by pressing Ctrl+X) and then paste it to a cell that's referenced by a formula. The formula will display #REF!.

Pay Attention to the Colors

When you edit a cell that contains a formula, Excel color-codes the cell and range references in the formula. Excel also outlines the cells and ranges used in the formula by using corresponding colors. Therefore, you can see at a glance the cells that are used in the formula.

You also can manipulate the colored outline to change the cell or range reference. To change the references that are used in the formula, drag the outline's border or fill handle (at the lower-right corner of the outline). Using this technique is often easier than editing the formula.

#VALUE! ERRORS

The #VALUE! error is very common and can occur under the following conditions:

• An argument for a function is of an incorrect data type or the formula attempts to perform an operation using incorrect data. For example, a formula that adds a value to a text string returns the #VALUE! error.

• A function's argument is a range when it should be a single value.

• A custom worksheet function is not calculated. With some versions of Excel, inserting or moving a sheet may cause this error. You can use Ctrl+Alt+F9 to force a recalculation.

• A custom worksheet function attempts to perform an operation that is not valid. For example, custom functions cannot modify the Excel environment or make changes to other cells.

• You forget to press Ctrl+Shift+Enter when entering an array formula.

Absolute/Relative Reference Problems

As I describe in Chapter 2, a cell reference can be relative (for example, A1), absolute (for example, $A$1), or mixed (for example, $A1 or A$1). The type of cell reference that you use in a formula is relevant only if the formula will be copied to other cells.

A common problem is to use a relative reference when you should use an absolute reference. As shown in Figure 21-2, cell C1 contains a tax rate, which is used in the formulas in column C. The formula in cell C4 is as follows:

 =B4+(B4*$C$1) 

Figure 21-2: Formulas in the range C4:C6 use an absolute reference to cell C1.

Notice that the reference to cell C1 is an absolute reference. When the formula is copied to other cells in column C, the formula continues to refer to cell C1. If the reference to cell C1 were a relative reference, the copied formulas would return an incorrect result.

Operator Precedence Problems

Excel has some straightforward rules about the order in which mathematical operations are performed in a formula. In Table 21-1, operations with a lower precedence number are performed before operations with a higher precedence number. This table, for example, shows that multiplication has a higher precedence than addition. Therefore, multiplication is performed first.

Table 21-1: OPERATOR PRECEDENCE IN EXCEL FORMULAS
Open table as spreadsheet

Symbol	Operator	Precedence
–	Negation	1
%	Percent	2
∘	Exponentiation	3
* and /	Multiplication and division	4
+ and –	Addition and subtraction	5
&	Text concatenation	6
=, <, >, and <>	Comparison	7

When in doubt (or when you simply need to clarify your intentions), use parentheses to ensure that operations are performed in the correct order. For example, the following formula multiplies A1 by A2, and then adds 1 to the result. The multiplication is performed first because it has a higher order of precedence.

 =1+A1*A2 

The following is a clearer version of this formula. The parentheses aren't necessary-but in this case, the order of operations is perfectly obvious.

 =1+(A1*A2) 

Notice that the negation operator symbol is exactly the same as the subtraction operator symbol. This, as you may expect, can cause some confusion. Consider these two formulas:

 =-3^2 =0-3^2 

The first formula, as expected, returns 9. The second formula, however, returns −9. Squaring a number always produces a positive result, so how is it that Excel can return the −9 result?

In the first formula, the minus sign is a negation operator and has the highest precedence. However, in the second formula, the minus sign is a subtraction operator, which has a lower precedence than the exponentiation operator. Therefore, the value 3 is squared, and the result is subtracted from zero, producing a negative result.

Note 

Excel is a bit unusual in interpreting the negation operator. Other spreadsheet products (for example, Lotus 1-2-3 and Quattro Pro) return −9 for both formulas. In addition, Excel's VBA language also returns −9 for these expressions.

Using parentheses, as shown in the following formula, causes Excel to interpret the operator as a minus sign rather than a negation operator. This formula returns 9.

 =-(3^2) 

Formulas Are Not Calculated

If you use custom worksheet functions written in VBA, you may find that formulas that use these functions fail to get recalculated and may display incorrect results. To force a single formula to be recalculated, select the cell, press F2, and then press Enter. To force a recalculation of all formulas, press Ctrl+Alt+F9.

Actual versus Displayed Values

You may encounter a situation in which values in a range don't appear to add up properly. For example, Figure 21-3 shows a worksheet with the following formula entered into each cell in the range B2:B4:

 =1/3 

Figure 21-3: A simple demonstration of numbers that appear to add up incorrectly.

Cell B5 contains the following formula:

 =SUM(B2:B4) 

All the cells are formatted to display with three decimal places. As you can see, the formula in cell B5 appears to display an incorrect result. (You may expect it to display 0.999.) The formula, of course, does return the correct result. The formula uses the actual values in the range B2:B4, not the displayed values.

You can instruct Excel to use the displayed values by checking the Set Precision as Displayed check box on the Advanced tab of the Excel Options dialog box. (Choose Office Excel Options to display this dialog box.) This setting applies to the active workbook.

Caution 

Use the Set Precision as Displayed option with caution, and make sure you understand how it works. This setting also affects normal values (nonformulas) that have been entered into cells. For example, if a cell contains the value 4.68 and is displayed with no decimal places (that is, 5), checking the Set Precision as Displayed check box converts 4.68 to 5.00. This change is permanent, and you can't restore the original value if you later clear the Set Precision as Displayed check box. A better approach is to use Excel's ROUND function to round the values to the desired number of decimal places (see Chapter 10). I've used Excel for many years, and I've never had a need to use the Set Precision as Displayed option.

Floating-Point Number Errors

Computers, by their very nature, don't have infinite precision. Excel stores numbers in binary format by using 8 bytes, which can handle numbers with 15-digit accuracy. Some numbers can't be expressed precisely by using 8 bytes, so the number stores as an approximation.

To demonstrate how this limited precision may cause problems, enter the following formula into cell A1:

 =(5.1-5.2)+1 

The result should be 0.9. However, if you format the cell to display 15 decimal places, you'll discover that Excel calculates the formula with a result of 0.899999999999999. This small error occurs because the operation in parentheses is performed first, and this intermediate result stores in binary format by using an approximation. The formula then adds 1 to this value, and the approximation error is propagated to the final result.

In many cases, this type of error does not present a problem. However, if you need to test the result of that formula by using a logical operator, it may present a problem. For example, the following formula (which assumes that the previous formula is in cell A1) returns FALSE:

 =A1=.9 

One solution to this type of error is to use Excel's ROUND function. The following formula, for example, returns TRUE because the comparison is made by using the value in A1 rounded to one decimal place.

 =ROUND(A1,1)=0.9 

Here's another example of a "precision" problem. Try entering the following formula:

 =(1.333-1.233)-(1.334-1.234) 

This formula should return 0, but it actually returns –2.220446E-16 (a number very close to zero).

If that formula were in cell A1, the following formula would return Not Zero.

 =IF(A1=0,"Zero","Not Zero") 

One way to handle these very-close-to-zero rounding errors is to use a formula like this:

 =IF(ABS(A1)<1E-6,"Zero","Not Zero") 

This formula uses the less-than operator to compare the absolute value of the number with a very small number. This formula would return Zero.

Phantom Link Errors

You may open a workbook and see a message like the one shown in Figure 21-4. This message sometimes appears even when a workbook contains no linked formulas.

Figure 21-4: Excel's way of asking whether you want to update links in a workbook.

First, try using Office Prepare Edit Links To Files to display the Edit Links dialog box. Then select each link and click Break Link. If that doesn't solve the problem, this phantom link may be caused by an erroneous name. Choose Formulas Defined Names Name Manager, and scroll through the list of names. If you see a name that refers to #REF!, delete the name. The Name Manager dialog box has a Filter button that lets you filter the names. For example, you can filter the lists to display only the names with errors.

Cross Ref 

These phantom links may be created when you copy a worksheet that contains names. See Chapter 3 for more information about names.

Logical Value Errors

As you know, you can enter TRUE or FALSE into a cell to represent logical True or logical False. Although these values seem straightforward enough, Excel is inconsistent about how it treats TRUE and FALSE.

Figure 21-5 Shows a worksheet with three logical values in A1:A3 as well as three formulas that sum these logical values in A5:A6. As you see, these formulas return three different answers!

Figure 21-5: This worksheet demonstrates an inconsistency when summing logical values.

The formula in cell A5 uses the addition operator. The sum of these three cells is 2. The conclusion: Excel treats TRUE as 1, and FALSE as 0.

But wait! The formula in cell A6 uses Excel's SUM function. In this case, the sum of these three cells is 0. In other words, the SUM function ignores logical values. However, it's possible to force these logical values to be treated as values by the SUM function by using an array formula. Enter the following formula using Ctrl+Shift+Enter, and it returns 2.

 =SUM(A1:A3*1) 

To add to the confusion, the SUM function does return the correct answer if the logical values are passed as literal arguments. The following formula returns 2:

 =SUM(TRUE,TRUE,FALSE) 

Although the VBA macro language is tightly integrated with Excel, sometimes it appears that the two applications don't understand each other. I created a simple VBA function that adds the values in a range. The function (which follows), returns –2!

 Function VBASUM(rng)     Dim cell As Range     VBASUM = 0     For Each cell In rng         VBASUM = VBASUM + cell.Value     Next cell End Function 

VBA considers True to be –1, and False to be 0.

The conclusion is that you need to be aware of Excel's inconsistencies and also be careful when summing a range that contains logical values.

Circular Reference Errors

A circular reference is a formula that contains a reference to the cell that contains the formula. The reference may be direct or indirect. For help tracking down a circular reference, see the following section, "Excel's Auditing Tools."

Cross Ref 

As described in Chapter 16, you may encounter some situations in which you create an intentional circular reference.