Spreadsheets and Data

In the tea shop, each row of the day’s sheet is one item: its price, how many were sold, and the money it brought in, the amount. The amount is the price times the number sold. So in D2, for milk tea, the formula is

=B2*C2

A spreadsheet uses * for times and / for divide, because a keyboard has no × or ÷ keys. As in math class, it does times and divide before plus and minus: =2+3*4 gives 14, not 20. Use brackets to change the order: =(2+3)*4 gives 20.

Copying a formula down

There are seven items. You could type seven formulas, =B2*C2, =B3*C3, =B4*C4 and so on. But a spreadsheet can do it for you: write the formula once, then copy it down the column. On a computer you drag the small square at the corner of the cell, the fill handle, down the column (or double-click it); on a phone you copy the cell and paste it into the cells below.

Look at what each copy says:

Cell	Formula	What it means
D2	=B2*C2	the price in this row times the number sold in this row
D3	=B3*C3	the same, one row down
D4	=B4*C4	the same, two rows down

The copies are not exact copies. Each one points to its own row. That is because a cell address in a formula is really a direction: =B2*C2 in D2 means “the cell two to my left, times the cell one to my left, in my row”. Copied one row down, the same direction leads to B3 and C3. An address that moves like this when it is copied is called a relative reference.

Fixing an address with $

Sometimes a formula must keep pointing to the same cell wherever it is copied. Say the tea shop wants each item’s share of the day’s money. Milk tea brought Rs 2,580 of Rs 6,630, so its share is 2,580 ÷ 6,630 × 100, about 39%. In E2 that is =D2/D9*100. Copy it down, and E3 says =D3/D10*100: the total moved down too, into an empty cell, and the answer is wrong.

A $ sign in front of a column letter or row number fixes it, so it doesn’t move when copied. $D$9 always means D9. An address fixed like this is called an absolute reference. The right formula is

=D2/$D$9*100

and copied down it becomes =D3/$D$9*100, =D4/$D$9*100…: each row’s own amount, divided by the one total.

In Grade 6 your class asked how each student comes to school, how far and how long, then sorted the answers and worked out totals and averages for the whole class. This time a Class 7 asked the same three questions, to compare the groups and look for connections:

• How do you usually come to school? (Walk, Bicycle, Motorbike, School bus, Public bus)
• About how far is it, in kilometers?
• About how many minutes does the trip take?

They wanted to find out: Which way of coming to school is fastest? Do the students who live farther away take longer? How long would a walk of 3 km take? These are questions the data can answer, which is the first step of any data investigation.

Instead of paper, they used an online form: a page of questions that anyone with the link can fill in on a phone. Each person’s answers arrive as one row of a spreadsheet, so 30 answers make a table of 30 records, ready to work on.

With 30 rows, the first tools are the ones that reorder and pick out rows.

Sorting, as you saw in Grade 6, puts every row in order by one column, whole rows moving together. Sorted by Minutes, descending, the longest trips come to the top. You can also sort by two columns: first by Comes by, then by Minutes within each group, so that all the walkers come together, fastest first.

Filtering shows only the rows you want and hides the rest. Filter the Comes by column to show only Walk, and the other 18 rows disappear from view. They are not deleted. Look at the row numbers: they jump (2, 4, 7…) where rows are hidden. Show all again, and every row comes back.

The most useful questions are often about groups. How many students walk? On average, how long does a bus rider’s trip take? To answer them for every group at once, you group the rows by one column and summarize each group: count it, add it up, or average it.

Three functions do this. Each one looks down one column, picks out the rows that match a rule, its criterion, and works only on those:

Formula	What it does	For the survey
=COUNTIF(B2:B31, "Walk")	counts the cells in B2:B31 that say Walk	12 students walk
=SUMIF(B2:B31, "Walk", C2:C31)	adds up the C cells of the rows whose B says Walk	the walkers walk 17.4 km in all
=AVERAGEIF(B2:B31, "Walk", D2:D31)	averages the D cells of the rows whose B says Walk	a walker’s trip takes 20.75 minutes on average

A word in a formula goes inside quotation marks, "Walk", so the spreadsheet knows it is a word and not a cell address. The criterion can also be a condition, like the conditions in Unit 3’s programs: =COUNTIF(D2:D31, ">=30") counts the trips of 30 minutes or more (10 of them), and "<>Walk" means anything except Walk.

Better still, the criterion can be a cell. Put the names of the groups in a column (Walk in A34, School bus in A35…), and write

=COUNTIF($B$2:$B$31, A34)

The $ signs fix the survey’s range, while A34 is relative. Copy the formula down, and each copy counts its own group: A35’s, A36’s… One formula, copied, makes a whole summary table.

Real data is messy. When people type their own answers, the same answer turns up in different words, numbers come with units attached, some boxes are left empty, and a few answers are plainly impossible. If you don’t notice, the spreadsheet happily gives wrong answers, and nothing warns you.

Here are the problems you will meet most often:

• The same answer, different words: “Walk”, “walking”, “walk ” (with a space at the end), “On foot”. To a computer these are four different words, so COUNTIF counts only one of them.
• A number stored as words: “3 km” or “Rs. 250”. A spreadsheet reads them as text, and SUM and AVERAGE quietly skip them.
• A wrong unit: 800 in a column of kilometers, from someone who meant 800 meters.
• A missing answer: an empty cell.
• A repeat: the same person’s answers twice, because they pressed Send twice.
• An impossible value: a 1 km walk that takes 150 minutes, an age of 112 in Class 7.

Finding and fixing these is called cleaning the data. For each problem there is usually more than one way to fix it, and the way you choose changes the answers you get.

Choosing a fix

There is rarely one right answer, but some fixes are better than others:

Problem	Good fixes	Poor fixes
Same answer, different words	change them all to one spelling	leaving them, or deleting the rows
Number stored as words	keep just the number; the unit goes in the header	leaving it, so AVERAGE skips it
Wrong unit	convert it (800 m = 0.8 km), if you are sure what was meant	leaving it
Missing	ask again; or leave it empty and say how many answered	putting 0, or inventing a value
Repeat	delete the extra copy	keeping both, so one person counts twice
Impossible	ask the person; or leave it out and say so	leaving it in, or quietly guessing

Two rules hold for every fix. Keep the original: clean a copy of the data, so a mistake can be undone. And write down what you changed and why (“Row 10: 150 minutes changed to 15, after asking Pasang”), so anyone can check your work.

The best fix of all is to stop the mess before it starts, when you design the form: give answer choices instead of a box to type in (no “On foot”), make number questions accept only numbers (no “3 km”), say the unit in the question (“in kilometers”), and make important questions required.

Ask “How long is your trip to school?” and you could record the answer in many ways: 22 minutes; 20 minutes (to the nearest 10); Medium; or not even per student, just “8 of us take under 15 minutes”. How exact a value is recorded is its precision: 22 minutes is more precise than 20, and 20 than “Medium”. Whether you record each person separately or only totals for the whole group is the data’s granularity: one row per student is fine-grained; one row per class is coarse.

More precise, finer-grained data can answer more questions. But it costs more: more care to collect, more space to store, and more risk to people’s privacy, because fine-grained data says exactly who did what.

So choose the precision before you collect, from the questions you want to answer:

• To find an average or a trend, you need the numbers themselves, not groups.
• Rounding can move a value across a line you care about: 28 minutes rounded to 30 lands in “30 minutes or more”.
• Groups are quick to answer and easy to count, and enough for a question like “How many take 30 minutes or more?”, as long as 30 is where a group starts.
• Totals keep people’s answers private, but can’t answer anything about one person.

Precision also decides storage. A phone that records where it is once a minute makes 1,440 records a day; once a second, 86,400, sixty times as much data to store and send, and a far more exact record of where its owner goes.

Recording a number more exactly than it was measured doesn’t make it more accurate. A student who guessed “about 20 minutes” didn’t time the trip; writing 20.0 doesn’t change that. Accuracy is how close a value is to the truth; precision is only how many details it gives.

Does a longer distance mean a longer trip? For the walkers, it should. To see a connection between two columns of numbers, draw a scatter chart: one dot for each student, placed across by the distance and up by the minutes.

If the dots climb from left to right in a rough band, the two go together: the farther, the longer. That pattern is a trend. A straight line drawn through the middle of the dots, as close to all of them as it can get, shows it. Spreadsheets call it a trend line, and they work it out for you.

A trend line lets you predict: a new student walks 2 km to school; reading up from 2 km to the line and across gives about 28 minutes. That is a fair guess, but only a guess. Real students sit above and below the line: Gita takes 28 minutes for her 2 km, but Rita takes only 26 for 2.2 km.

Predictions have limits:

• Stay inside the data. The walkers live between 0.3 and 3 km away. The line says a 10 km walk takes over two hours, but nobody in the data walks that far: on such a long walk people tire and rest, or take a bus. Predicting outside the data is called extrapolation, and it is often wrong.
• Same kind of people. The line is for walkers. A bus rider 7 km away takes about 30 minutes, not the 92 the walkers’ line would predict.
• Going together isn’t the same as causing. Here a longer distance really does cause a longer walk. But in many surveys two things rise together because of a third (in a hot week, both ice-cream sales and drownings go up; ice cream doesn’t make people drown). A trend shows that two things go together; it doesn’t prove that one causes the other.

A budget is a plan for money: what comes in, what goes out, and what is left. A spreadsheet is the natural place for one, because when one number changes, everything that depends on it changes too. That lets you ask “what if?” questions, and see the answer at once.

A family’s month might look like this: money in from a salary and a vegetable stall; money out for rent, food, school fees, cooking gas, electricity, phone data and bus fares. Three formulas do the work: the total in, the total out, and what is left:

Left to save = Total in − Total out

A small shop’s budget works the same way, with sales as the money in and costs as the money out. What is left is the shop’s profit; if costs are bigger than sales, it is a loss.

A data investigation isn’t finished when the formulas are done. Someone has to read what you found and decide whether to trust it. So the last step is a short summary of the whole investigation, honest about what it can’t show.

A good summary answers five questions:

1. The question. What did you want to find out?
2. The data. Who did you ask, how, and when? How many answered?
3. What you did. How did you clean the data, and what did you work out?
4. What it showed. The findings, each with its evidence: the numbers or the chart that show it.
5. How sure, and the limits. What could make the findings wrong, and what can’t the data tell you?

The limits are where most summaries go wrong. Look out for bias: anything that tilts the data one way, so it no longer stands for what you wanted to know about.

• Who was asked. One class isn’t the whole school. Students in Class 7 may live in different places from those in Class 1.
• Who answered. If the form was shared online, students without a phone at home couldn’t answer, and they may be the ones who walk farthest.
• How it was asked. A leading question, or choices that don’t fit everyone, push the answers.
• How it was measured. “About how many minutes?” gives guesses, not timed trips.
• How small. A pattern in 3 public bus riders could be chance; with 3 more it might vanish.