Formulas are the most complicated data element in a spreadsheet. This concept looks at the most common mistakes made when entering a formula.
Punctuation. Simple punctuation errors can cause vexing errors. Some of the most common are,
Using ";" in place of ":". For example, B1:B3 is correct, B1;B3 is incorrect.
Using "." to separate arguments in a function call rather than ",". For example, IF(B1>=0,"Positive","Negative") is correct, IF(B1>=0,"Positive"."Negative") is incorrect.
Mixing "." and "," when entering a number. Excel ignores ","s you type while entering a number. (You can format the cell to display ","s, but the commas will be displayed in the correct place based on the value of the number. Again, there is a difference between the value a cell contains and the display of that value in the grid area.) If you enter a comma in the place of a period, the comma will be ignored and you will end up with a number much larger than you expected. For example, 19,99 ==> 1,900.0. If you type a period when you intended to type a comma, you will likely end up with a number much smaller than you expected. For example, 1.200 ==> 1.2.
Function name misspelled. Excel has many predefined functions you can use. The best way to include a function in your document is to use the insert function command. If you type the name from the keyboard and it doesn't match an existing function, you will get an error message. If, however, by coincidence you type the name of a different existing function, you won't get an error message, just incorrect results.
Order of evaluation. There are precise rules that govern the order of evaluation for expressions. (Expressions with parenthesis are evaluated first, multiplication has a higher priority than addition, etc.) Subtle errors can be introduced when invalid assumptions about the evaluation order are made. For example, (B1+B2) / A1+A2 is equivalent to ((B1+B2) / A1) + A2, not (B1+B2) / (A1+A2).
Unbalanced parentheses. The best way to prevent order of evaluation problems is to use parentheses generously. This solution increases the potential for another type of problem, though, which is unbalanced parentheses. When entering a complex formula it's not uncommon to have one too many or one too few parenthesis. For example, (A1+B2)*(C3)/(D4-E5)) is incorrect.
Logic problems. Errors are sometimes introduced when forming or combining expressions. When in doubt about how to combine or reduce an equation, consult an introductory algebra or logic book. Two of the most common mistakes here are:
Relative vs. absolute reference. Problems can also be introduced when you mix relative and absolute references within a formula. When you move a formula relative references are adjusted accordingly and absolute references stay the same. For example, if you move the formula =A1+$A2+$A$3 right two cells and down one cell, it will be converted to =C2+$A3+$A$3. The mistake that is usually made is forgetting to code an absolute reference, or inserting the "$" only in front of the column reference.
Place holders in inserted functions. When you insert a function from the insert function menu, Excel adds place holders in the argument area of the function. For example:
Place holders are meant to remind you what values can or must be included as an argument to the function. If you don't remove or type over these place holders, you will get an error message as soon as you commit your formula.
Copyright 1996 by the Curators of the University of Missouri