You must have an equals sign ( = ) as the first character in a cell that contains a formula.
The = sign tells excel that the contents of the cell is a formula
Without the = sign, the formula will not calculate anything. It will simply display the text of the formula.
Formulas - correct
formula with = sign
After pressing ENTER
Missing = sign
Missing = sign! Before pressing enter
After pressing ENTER (no change - not a function)
Types of operations
You can use any of the following operations in a formula: operation symbol example
addition: + =a1+3
subtraction: - =100-b3
multiplication: * =a1*b1
division: / =d1/100
exponentiation ^ =a2^2
negation - =-a2+3 (same symbol as subraction)
Explicit (literal) values and cell references
You can use both explicit values and cell references in a formula
An explicit value is also called a literal value
Formula with only cell references: =a1*b1
Formula with only literal values: =100/27
Formula with both cell references and literal values: =a1/100
Errors in Formulas
Common Errors
The following are some errors that may appear in a spreadsheet (there are others too).
#######
Cell is too narrow to display the results of the formula. To fix this simply make the column wider and the “real” value will be displayed instead of the ###### signs. Note that even when the ###### signs are being displayed, Excel still uses the “real” value to calculate formulas that reference this cell.
#NAME?
You used a cell reference in the formula that is not formed correctly (e.g. =BB+10 instead of =B3+10)
#VALUE!
Usually the result of trying to do math with a textual value. Example: =A1*3 where A1 contains the word “hello”
#DIV/0!
Trying to divide by zero. Example: =3/A1 where A1 contains 0 (zero)
Circular Reference
Using a formula that contains a reference to the cell that the formula “lives in”. Example: putting the formula =A1+1 in cell A1 or putting the formula =SUM(A1:B2) in any of the cells A1, B1, A2, B2
