Spreadsheets: formulas and sorting
Be able to use formulas, sorting and filtering in a spreadsheet.
Prerequisites
Intuition
A spreadsheet (Google Sheets, LibreOffice Calc, Excel) is a table where cells can contain formulas. Cell C2 can be =A2*B2 — change A2 and C2 is recalculated automatically.
Common formulas: =SUM(B2:B20), =AVERAGE(B2:B20), =MAX(B2:B20), =COUNTIF(C2:C20,"yes").
Sorting orders the rows by a column. Filtering shows only the rows that meet a condition. These are the same operations as sorted() and an if inside a loop in Python — and this is how you look at training data before you train anything.
Interactive
Do this in a spreadsheet:
| A: name | B: time (s) | C: passed |
|---|---|---|
| Ali | 42 | yes |
| Bea | 55 | no |
| Cem | 38 | yes |
=AVERAGE(B2:B4)→ 45.=COUNTIF(C2:C4,"yes")→ 2.- Sort by B ascending → Cem, Ali, Bea.
- Filter C = yes → Ali, Cem.
Add 20 made-up rows and watch the formulas follow along. Build a chart from B. This is «exploratory data analysis» in miniature.
Mastery means
- Uses formulas with cell references, SUM and AVERAGE
- Sorts and filters a table
Sign in to do the exercises and build your mastery up.
Sources
- LibreOffice Calc — help (MPL-2.0) — MPL-2.0