The essentials, close at hand.
Use your browser’s Print command to print this reference or save it as a PDF.
Addresses and ranges
B3: column B, row 3. B2:B6: every cell from B2 through B6, including both endpoints.
Start with =
=B2*C2 multiplies two inputs. =(B2-C2)/B2 calculates profit margin when B2 is revenue and C2 is cost. Multiplication and division happen before addition and subtraction. Use parentheses to change the order.
Core function syntax
=FUNCTION(number1, [number2], ...)
number1 is required. Further inputs are optional. Use a number, cell reference or range. Square brackets and dots describe the syntax; do not type them. Some regional settings use semicolons in place of commas.
| A | B | |
|---|---|---|
| 1 | Question | Example |
| 2 | Total | =SUM(B2:B6) |
| 3 | Arithmetic mean | =AVERAGE(B2:B6) |
| 4 | Smallest number | =MIN(B2:B6) |
| 5 | Largest number | =MAX(B2:B6) |
Empty is not zero
In referenced ranges, these four functions ignore empty cells and text but include numeric zeros. AVERAGE divides by the count of numeric values. An average of no numeric values returns #DIV/0!.
Format the result for its meaning
A stored value of 0.15 displays as 15% with percentage formatting. Number formatting changes the display, not the underlying value used in calculations.
Copy with the right anchors
B2 moves with a copied formula. $B$2 stays fixed. $B2 fixes the column; B$2 fixes the row. In a two-way grid, =$B2*C$1 can read the price from column B and rate from row 1.
Count and decide
=COUNT(B2:B6) counts numeric entries, including zero. =COUNTA(B2:B6) counts nonempty entries, including text. A formula returning empty text still counts in COUNTA.
=IF(B2>=100,"Free","Paid") returns Free at 100 or above, otherwise Paid. Supply a condition, a true result and an explicit false result. Quote text labels and test threshold boundaries.