Introduction to Excel Yitzchak Rosenthal

Formulas

The bread and butter of Excel

Excel Formulas

• 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