What a formula does
A formula is an instruction stored in a cell. It begins with an equals sign and tells the spreadsheet to calculate something using numbers, text or other cells, then to show the result in that cell. Because formulas refer to cells rather than typed numbers, they recalculate whenever those cells change. That is the real power of a spreadsheet: write the rule once and let it keep itself current.
Functions are ready-made formulas with names, such as the one that adds numbers. A function is written as its name followed by brackets that contain what it should work on. Exact names and the character separating items inside brackets can differ between programs and language settings, so check your own tool’s help if an example does not work.
The building blocks
- Operators are the symbols for arithmetic: plus, minus, an asterisk for multiplication and a slash for division.
- Cell references such as B2 point to a cell’s value.
- Ranges such as B2:B6 cover a block of cells.
- Functions do larger jobs on a range.
Brackets control the order of operations, as in ordinary math. Multiplication and division happen before addition and subtraction unless brackets say otherwise, so =2+3*4 gives 14, while =(2+3)*4 gives 20.
A hand-checkable data set
Use this hypothetical column of five daily sales figures in cells B2 to B6:
| Cell | Value |
|---|---|
| B2 | 12 |
| B3 | 18 |
| B4 | 15 |
| B5 | 20 |
| B6 | 10 |
Sum
=SUM(B2:B6) adds the range. Working it out: 12 + 18 + 15 + 20 + 10 = 75.
Average
=AVERAGE(B2:B6) adds the values and divides by how many there are. That is 75 divided by 5, which is 15. Blank cells are ignored by the average, while a typed zero is included, which can change the result. When values are uneven, remember the mean can be pulled by extremes, a point explored under the median average.
Count
=COUNT(B2:B6) counts how many cells contain numbers, which is 5 here. A related function counts non-empty cells including text, and another counts blanks. Counting is a quick way to confirm that a range covers all your records.
Minimum and maximum
The minimum function returns 10, and the maximum returns 20.
Simple conditions
Often you want to act on only some values. A condition compares something and gives a result that is true or false.
The IF idea
An IF formula has three parts: a test, a result if the test is true, and a result if it is false. For example, =IF(B2>14,"High","Low") checks whether B2 is greater than 14. For B2, 12, the answer is “Low”. Copied down the column, it gives Low, High, High, High, Low for 12, 18, 15, 20 and 10 respectively. Check: 18, 15 and 20 are above 14, while 12 and 10 are not.
Counting with a condition
A conditional count tallies the cells that meet a test. Counting values greater than 14 in B2:B6 gives 3, since 18, 15 and 20 qualify.
Summing with a condition
A conditional sum adds only the matching cells. Adding values greater than 14 gives 18 + 15 + 20 = 53. As a cross-check, the values that do not qualify are 12 + 10 = 22, and 53 + 22 = 75, matching the full sum.
Using categories
The same idea works on text. If column A holds a product name and column B an amount, a conditional sum can total the amounts for only one product. This is the basic form of what pivot tables do for every category at once.
Absolute and relative references
When you copy a formula down, references shift to follow it, so =B2*C2 becomes =B3*C3 one row lower. Sometimes you want one reference to stay put, such as a single cell holding a rate. Adding dollar signs, as in $F$1, fixes it. Misusing these is a frequent source of puzzling results, one of the usual spreadsheet errors.
Common formula problems
- Range too short or too long. Check the highlighted cells.
- Text instead of numbers. Values that look like numbers but are stored as text are skipped.
- Circular references, where a formula refers to its own cell, directly or indirectly. Programs usually warn you.
- Error messages, such as a division by zero message, mean something in the formula cannot be calculated. Read the message type; it often points to the cause.
- Typed numbers hiding inside formulas. If a value might change, put it in its own labeled cell.
Habits for readable formulas
- Keep each formula as simple as it can be. A long nested formula is hard to check; use helper columns to break the job into steps.
- Label columns clearly, so a formula’s purpose is evident.
- Test with a small case you can calculate by hand, as above, before applying it to a large list.
- View all formulas at once to look for inconsistency in a column.
- Avoid copying and pasting values over formulas unless you intend to freeze them.
Where formulas fit
Formulas are the backbone of everything else in a spreadsheet. A budget uses them to total categories and subtract from income. A dashboard uses them to calculate key figures. Time tracking or simple project lists, the sort of thing you might also manage with productivity tools, benefit from them too. Once you are comfortable with these few, you can look up more functions when you have a specific need, without feeling you must learn them all.