Chrysolites Tech Academy

Programming Foundations: Python, Algorithms, Kotlin and Excel · Course book

Chapter 4: How Spreadsheet Formulas Think

Pairs with Lesson 4: Excel Formulas That Do Real Work

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.

RowA: RegionB: ProductC: UnitsD: PriceE: Total
2NorthPens102.5025.00
3SouthPens42.5010.00
4NorthBooks38.0024.00
5EastPens62.5015.00
6SouthBooks58.0040.00
7NorthPens82.5020.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.

=E2*$H$1=E3*$H$1=E4*$H$1 Formula in F2, copied down to F3 and F42.501.002.40 0.1 cell H1 The row number in E changes on each line; $H$1 never moves.
Figure 4.1 — Relative and absolute references in one formula. Copying changes E2 to E3 and E4, but the dollar signs keep every copy pointed at the tax rate in 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.

Keep constants in cells. Type a rate such as 0.1 into one labelled cell and refer to it, instead of typing 0.1 inside twenty formulas. When the rate changes you edit one cell and the whole sheet follows.

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.

695015 NorthSouthEastregion 0255075
Figure 4.2 — Total sales by region, each bar produced by one SUMIFS formula. The three bars add up to 134, the same as the plain SUM of column E.

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.

ErrorMeaningTypical cause
#DIV/0!Division by zeroDividing by an empty cell
#N/AValue not availableA lookup that found no match
#NAME?Unknown nameA misspelled function, or text without quotation marks
#REF!Invalid referenceA formula pointing at a deleted row or column
#VALUE!Wrong kind of valueDoing arithmetic on text
#SPILL!No room to spillOther 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?

Install Chrysolites Tech Academy

Android / Chrome / Edge: open the browser menu and choose Install app or Add to Home screen.

iPhone / iPad (Safari): tap the Share button, then Add to Home Screen.