In Sessions 19 and 20 you typed numbers, wrote formulas, used SUM, AVERAGE, MAX and MIN, and drew a chart. In Session 21 your class answered a survey. Now you have a sheet full of real answers, called data. Today you learn to ask it questions: Who has the longest journey? How many of us walk? What food wins? Then you will tell the class what you found, in one clear sentence and one clear chart.
Think of your whole school at morning assembly. The head teacher can do four clever things with all those pupils, and a spreadsheet can do exactly the same four things with rows of data:
Here are the first rows of the class survey file (Class_Survey). It has 20 rows of answers, from row 2 down to row 21.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | No. | Class | Travel | Minutes | Food | Screen hours | Enjoy (1–5) |
| 2 | 1 | JHS 1 | Walk | 15 | Jollof | 2 | 5 |
| 3 | 2 | JHS 2 | Trotro or bus | 40 | Waakye | 4 | 4 |
| 4 | 3 | JHS 1 | Walk | 10 | Banku | 1 | 5 |
| … | …and 17 more rows, down to row 21. | ||||||
When you scroll down a long sheet, row 1 disappears and you forget which column is which. Freezing pins it to the top. In Excel: View → Freeze Panes → Freeze Top Row. Scroll down: the header stays!
Google Sheets: View → Freeze → 1 row. LibreOffice Calc: View → Freeze Cells → Freeze First Row.
Sorting puts the rows in order: A to Z (or Z to A) for words, smallest to largest (or largest to smallest) for numbers. But sorting has one famous trap:
Google Sheets: freeze row 1 first, then Data → Sort sheet → Z to A. LibreOffice: Data → Sort Descending. Made a mess? Ctrl + Z straight away!
A filter hides the rows you don't want to see right now, like asking only JHS 2 to step forward. Nothing is deleted: clear the filter and every row comes back.
You met =SUM and =AVERAGE in Session 20. COUNTIF counts how many cells in a range match a rule. It has two parts inside the brackets, separated by a comma:
C2:C21 means "the Travel answers, C2 through to C21". Leave out row 1, the header.
"Walk" in quotation marks for words, spelt exactly like the cells. For numbers: 5, or ">4" for "more than 4".
Cells E2 to E7 contain: Jollof, Waakye, Jollof, Fufu, Jollof, Waakye. What does each formula show?
| Formula | Result |
|---|---|
=COUNTIF(E2:E7,"Jollof") | |
=COUNTIF(E2:E7,"Waakye") | |
=COUNTIF(E2:E7,"Kenkey") |
IF checks a test for every row and writes one answer when the test is true and a different answer when it is false. Imagine a teacher marking a test: "If the score is 50 or more, write Pass; if not, write Try again."
Your turn: what does =IF(B2>=50,"Pass","Try again") show when B2 is…
Conditional formatting colours cells automatically when they match a rule, like a highlighter that reads. Select F2:F21 (screen hours) → Home → Conditional Formatting → Highlight Cells Rules → Greater Than → type 4 → OK. Every value over 4 turns light red, and the colour updates if a number changes. Google Sheets: Format → Conditional formatting. LibreOffice: Format → Conditional → Condition.
Make your chart from your small COUNTIF summary table (the labels and the counts), not from the 20 raw rows. Remember Session 20: select the labels and the numbers, give the chart a title that says what it shows, and leave out any total. Then write a finding: one sentence that states a fact from the data, with a number.
"Half of our class (10 of 20) walk to school."
"Every pupil who comes by trotro or bus takes 40 minutes or more."
"Pupils love walking." (The data didn't ask that.)
"Trotros are bad." (An opinion, with no number.)
Warm up in the Data Sorter 3D lab on your session page: sort and filter a table of survey answers and use IF to label each row, while the 3D columns rearrange themselves. Then open Class_Survey from Documents > BIH_Practice (your class's Session 21 answers, or the sample your mentor gives you) and save a copy as Class_Survey_YourName.
Trip. In H2 type =IF(D2>=30,"Long trip","Short trip"), press Enter, then drag the AutoFill handle down to H21.Travel and in K1 Pupils. In J2 to J6 type Walk, Trotro or bus, Taxi, Bicycle, Car. In K2 type =COUNTIF(C2:C21,"Walk"), and so on for each one. In J8 type Long trips and in K8 =COUNTIF(H2:H21,"Long trip").How Our Class Travels to School. Save with Ctrl + S.| Question | My result |
|---|---|
| Longest journey: which row is on top? (Class, travel, minutes, food) | |
| How many rows show after filtering for Trotro or bus? | |
| COUNTIF results: Walk / Trotro or bus / Taxi / Bicycle / Car | |
| How many long trips? How many cells glow for screen time? |
My three findings (one sentence each, with a number). Share your best one with the class!
A. Yaw wants the survey rows in order of minutes. What should he do?
B. Akosua filtered the sheet to show only JHS 3. Now she thinks the other rows were deleted. What is true?
C. Which formula counts how many pupils chose Waakye in E2:E21?
=SUM(E2:E21,"Waakye")=COUNTIF(E2:E21,"Waakye")=COUNTIF(Waakye)D. Which is a good finding?
Question 1: Write an IF formula for cell H2 that shows "Too much" when the screen hours in F2 are more than 4, and "OK" when they are not.
Question 2: Why should you leave an empty column between your data and your COUNTIF summary table?
Tick the column that reflects your confidence at the end of Session 22:
| Working with Data Competency | With Help | Almost Alone | All By Myself! |
|---|---|---|---|
| 1. I can freeze the header row and sort data without breaking the rows. | |||
| 2. I can filter data and count answers with COUNTIF. | |||
| 3. I can write an IF formula and use conditional formatting. | |||
| 4. I can make a clear chart of survey results and write a one-sentence finding. |
BIH JHS Tech Club · a Bozoma Innovation Hub programme · Your answers are saved only on this computer.