Assume this table: products in column A, quantities in B and prices in C, rows 2 to 10.
| Formula | What it does | Example |
|---|---|---|
| SUM | Adds a range | =SUM(B2:B10) |
| AVERAGE | Average of a range | =AVERAGE(C2:C10) |
| IF | Chooses between two results | =IF(B2<10,"Reorder","OK") |
| SUMIF | Adds only what meets a condition | =SUMIF(A2:A10,"Coffee",B2:B10) |
| COUNTIF | Counts cells meeting a condition | =COUNTIF(B2:B10,"<10") |
| VLOOKUP | Finds a value and returns data from its row | =VLOOKUP("Tea",A2:C10,3,FALSE) |
| ROUND | Rounds to N decimals | =ROUND(C2*1.08,2) |
IF step by step
=IF(test, value_if_true, value_if_false). Example: =IF(D2>=1000,D2*0.05,0) gives a 5% discount on sales of $1,000 or more.
VLOOKUP step by step
=VLOOKUP(lookup_value, table, column_number, FALSE). It searches the first column of the table and returns the value from the given column. Always use FALSE for an exact match, or it may return the wrong row.
Absolute references ($)
When you copy a formula, references shift automatically. To lock a cell, use $: =B2*$F$1 always multiplies by F1, such as a tax rate.
Common errors
- #NAME?: the function name is misspelled.
- #N/A: VLOOKUP did not find the value.
- #DIV/0!: dividing by zero or an empty cell.
Practice with the Inventory template in TS Sheets: it already includes multiplication, IF and SUM formulas.
How to open a Word (.docx) file without Office
How to open a PowerPoint without PowerPoint