Programming Foundations: Python, Algorithms, Kotlin and Excel · Course book
Chapter 4: How Spreadsheet Formulas Think
A spreadsheet is a programming environment in disguise. Each formula is a small program: it takes inputs from other cells, applies logic and produces a result that updates the moment its inputs change. This chapter explains how formulas refer to cells, make decisions, total selected rows, look values up and return whole lists, using one small sales sheet throughout so that every answer can be checked by hand.
Cells, references and copying
Every cell has an address made from its column letter and row number, such as C2. A formula begins with an equals sign and may use addresses in place of numbers. All the examples in this chapter use the sheet in Table 4.1, where row 1 holds the headings and the Total column is worked out by a formula.
| Row | A: Region | B: Product | C: Units | D: Price | E: Total |
|---|---|---|---|---|---|
| 2 | North | Pens | 10 | 2.50 | 25.00 |
| 3 | South | Pens | 4 | 2.50 | 10.00 |
| 4 | North | Books | 3 | 8.00 | 24.00 |
| 5 | East | Pens | 6 | 2.50 | 15.00 |
| 6 | South | Books | 5 | 8.00 | 40.00 |
| 7 | North | Pens | 8 | 2.50 | 20.00 |
Cell E2 contains =C2*D2, which gives 10 × 2.50 = 25.00. You do not retype the formula for the other rows. You copy E2 downwards, and the spreadsheet adjusts it: E3 becomes =C3*D3, E4 becomes =C4*D4, and so on. This is a relative reference. The formula does not really mean "C2"; it means "the cell two columns to my left".
Sometimes that adjustment is exactly what you do not want. Suppose a tax rate of 0.1 is stored once, in cell H1, and column F should show the tax on each total. Writing =E2*H1 works in row 2, but copied down it becomes =E3*H2, and H2 is empty. The fix is an absolute reference, written with dollar signs: =E2*$H$1. The dollar signs pin the column and the row, so every copy still points to H1.
The results are easy to confirm: 25.00 × 0.1 = 2.50, 10.00 × 0.1 = 1.00 and 24.00 × 0.1 = 2.40. You can also pin just one part. $H1 fixes the column only and H$1 fixes the row only, which is useful when a formula is copied across as well as down.
Making decisions
The IF function chooses between two results. It takes three parts: a test, the result when the test is true and the result when it is false.
=IF(C2>=8, "Bulk", "Standard")
In row 2 the units are 10, so the test is true and the cell shows Bulk. Row 3 has 4 units and shows Standard. Copied down the sheet, rows 2 and 7 are Bulk and the other four are Standard.
Tests can be combined. AND is true only when every condition inside it is true, and OR is true when at least one is. The formula =IF(AND(A2="North", C2>=8), "Priority", "") marks rows 2 and 7, the two northern orders of eight units or more, and leaves the others blank.
When there are more than two outcomes, nesting one IF inside another quickly becomes hard to read. IFS lists pairs of tests and results and returns the first one that matches:
=IFS(C2>=8, "Bulk", C2>=5, "Medium", TRUE, "Small")
Order matters, because checking stops at the first true test. The final pair uses TRUE as a catch-all for anything that reached the end. Down the six rows the results are Bulk, Small, Small, Medium, Medium and Bulk.
Totals that follow conditions
SUM adds a whole range, so =SUM(E2:E7) gives 25 + 10 + 24 + 15 + 40 + 20 = 134. More often you want the total for only some rows, and that is the job of SUMIFS. Its first part is the range to add; after that come pairs made of a range to check and the value to look for.
=SUMIFS(E2:E7, A2:A7, "North")
=SUMIFS(C2:C7, A2:A7, "North", B2:B7, "Pens")
The first formula adds the totals on rows 2, 4 and 7, the three northern rows: 25 + 24 + 20 = 69. The second adds units where the region is North and the product is Pens, which is rows 2 and 7: 10 + 8 = 18. Every condition must be met for a row to count.
Two relatives follow the same pattern. =COUNTIFS(B2:B7, "Books") counts matching rows and returns 2. =AVERAGEIFS(C2:C7, B2:B7, "Pens") averages the units on the four pen rows: (10 + 4 + 6 + 8) ÷ 4 = 7.
Checking that the parts add up to the whole, as in Figure 4.2, is a simple habit that catches many mistakes, such as a region spelled two different ways.
Looking things up
A lookup answers the question "find this value in one column and give me what sits beside it in another". The modern tool is XLOOKUP, which takes the value to find, the range to search and the range to return from.
=XLOOKUP("Books", B2:B7, D2:D7)
=XLOOKUP("Ink", B2:B7, D2:D7, "Not found")
The first formula searches the product column for Books, finds it first on row 4 and returns the price from the same row, 8.00. The second searches for a product that is not there. Without the fourth part the cell would display an error; with it, the cell shows the text Not found.
XLOOKUP replaces the older VLOOKUP, which you will still meet in inherited workbooks. VLOOKUP could only search the first column of a table and return something to its right, and it identified the answer column by counting, so inserting a new column silently broke the formula. XLOOKUP names the search range and the return range separately, which removes both weaknesses.
The same lookup can be built from two smaller functions. XMATCH reports the position of a value within a range, and INDEX returns whatever sits at a given position.
=INDEX(D2:D7, XMATCH("Books", B2:B7))
Here XMATCH returns 3, because Books is the third entry in B2:B7, and INDEX then returns the third entry in D2:D7, which is 8.00. Splitting the work this way is handy when you need the position itself, or want to look along a row and down a column at the same time.
Cleaning data, dynamic arrays and errors
Data arriving from other systems is rarely tidy. Text functions repair it. TRIM removes spaces from the start and end and squeezes repeated spaces inside the text down to one. UPPER, LOWER and PROPER change capitalisation, while LEFT, RIGHT and MID cut out part of a text. The ampersand joins pieces together, so =A2&" - "&B2 gives North - Pens.
Dates look like text but are stored as numbers, counted in days. That is why subtracting one date from another gives the number of days between them: =DATE(2026,3,1)-DATE(2026,2,1) returns 28, the length of February 2026.
Older formulas return one value to one cell. A dynamic array formula returns a whole list, which spills into as many neighbouring cells as it needs.
=UNIQUE(A2:A7)returns each region once: North, South and East, filling three cells.=SORT(UNIQUE(A2:A7))returns the same three in alphabetical order: East, North, South.=FILTER(A2:E7, A2:A7="North")returns the three complete northern rows.
You type the formula in one cell only. If the source data changes, the spilled list grows or shrinks by itself. Finally, when something goes wrong a cell shows an error code. Each code in Table 4.2 names a particular kind of problem, so read it as a clue. Wrapping a formula as =IFERROR(formula, "message") replaces any error with a message you choose, but use it only once you understand why the error appears, because it can also hide real mistakes.
| Error | Meaning | Typical cause |
|---|---|---|
#DIV/0! | Division by zero | Dividing by an empty cell |
#N/A | Value not available | A lookup that found no match |
#NAME? | Unknown name | A misspelled function, or text without quotation marks |
#REF! | Invalid reference | A formula pointing at a deleted row or column |
#VALUE! | Wrong kind of value | Doing arithmetic on text |
#SPILL! | No room to spill | Other content is blocking the cells a dynamic array needs |
Practice tasks
Type Table 4.1 into a blank sheet, with the headings in row 1, and try the following. Work out the expected answer on paper first, then check it against the sheet.
- Write a formula for the total sales in the South region. (Expected answer: 10 + 40 = 50.)
- Write a formula that counts the orders of more than 5 units. (Expected answer: 3, for rows 2, 5 and 7. Hint: the condition is written as
">5".) - Put a discount rate of 0.2 in cell H2 and add a column showing each total after the discount, using an absolute reference. (Row 6 should show 40 × 0.8 = 32.)
- Use XLOOKUP to return the region of the first order for Books. (Expected answer: North.)
- Use SORT and UNIQUE together to list the products in alphabetical order. (Expected answer: Books, Pens.)
- Change the units in row 5 from 6 to 60 and watch which results update. Which formulas did not change, and why?
This course book is available to read online only at tech-academy.chrysolites.org.