Computer Science - ICT Grade 6 Spreadsheets Formulas Charts Data Analysis

Grade 6 Computer Science (ICT) Study Guide: Spreadsheets – Formulas, Charts, Data Analysis

1. The Driving Question

"If you had a whole summer’s worth of lemonade stand sales—dozens of days, different prices, and weather conditions—how could you turn all that messy data into one clear answer: What actually made you the most money? And how do you prove it to your parents so they’ll let you upgrade your stand?"

2. The Core Idea – Built, Not Listed

Imagine your lemonade stand is a spreadsheet—not a grid of numbers, but a live calculator that updates itself the second you change a price or add a new day. You type =B2*C2 in cell D2, and suddenly, every sale’s total appears instantly. That = is the spreadsheet’s way of saying, "I’m not just storing this—I’m working for you."

Now, you want to know: Did hot days sell more? Did raising the price backfire? Instead of guessing, you sort the data by temperature, filter out rainy days, and sum the profits. Then, you drag a bar chart onto the screen, and—click—the answer jumps out: "Hot days at $1.50 were the sweet spot." The spreadsheet didn’t just organize your data; it let you ask it questions and showed you the story hiding in the numbers.

Key Vocabulary:
- Formula – A command that tells the spreadsheet to calculate something.
Definition: A rule you write (starting with =) that combines numbers, cells, or functions to produce a result.
Example: =AVERAGE(B2:B10) calculates the average of lemonade cups sold from Day 1 to Day 9.
Not the usual example: =SUMIF(C2:C10, ">25", D2:D10) adds up profits only on days hotter than 25°C.

• Cell Reference – The "address" of a box in the spreadsheet (like B3 or D12).
  Definition: The column letter + row number that tells the formula where to look for data.
  Example: In =B2*C2, B2 is the number of cups sold, and C2 is the price per cup.
  Why it matters: If you copy =B2*C2 down to row 3, it automatically becomes =B3*C3—no retyping!

• Chart – A picture that turns numbers into a pattern you can see at a glance.
  Definition: A visual representation of data that highlights trends, comparisons, or relationships.
  Example: A line chart of daily temperatures vs. sales shows if hotter days always mean more lemonade sold (or if there’s a point where it gets too hot).
  Not the usual example: A pie chart of profits by flavor (strawberry vs. classic) might reveal that "mystery flavor" is a flop—even if it’s your favorite.

• Function – A pre-built formula (like SUM or AVERAGE) that does a specific job.
  Definition: A shortcut command that performs a common calculation, so you don’t have to write it from scratch.
  Example: =MAX(D2:D10) instantly finds your best day’s profit, while =MIN(D2:D10) finds your worst.
  Grade 6 tip: Functions can be nested—like =ROUND(AVERAGE(B2:B10), 0) to round the average cups sold to a whole number.

3. Assessment Translation

How This Appears on State/ICT Assessments:
- Multiple Choice: Tests reading formulas (e.g., "What does =SUM(A1:A5) calculate?") or interpreting charts (e.g., "Which month had the highest sales?").
Distractor patterns: - Confusing SUM with AVERAGE (e.g., "It adds all the numbers and divides by 5").
- Misreading cell references (e.g., "A1:A5 means cells A1 and A5").
- Chart misinterpretation (e.g., picking the tallest bar instead of the highest value).

• Short Constructed Response: "Explain how you would use a spreadsheet to find the total profit for a bake sale. Include the formula you would use." Proficient response: "I would multiply the number of items sold (column B) by the price per item (column C) using =B2*C2, then drag the formula down. Finally, I’d use =SUM(D2:D10) to add up all the profits in column D." Developing response: "I would add up all the money in column D." (Missing how the data gets to column D.)

• Performance Task: Given a dataset (e.g., zoo animal weights), students must:

• Write a formula to calculate the average weight.
• Create a chart comparing weights by species.
• Answer: "Which species is the heaviest on average? Justify your answer using the chart." What teachers look for:
• Correct use of AVERAGE (not SUM or MAX).
• A chart that matches the question (e.g., bar chart for comparisons, not a pie chart).
• Evidence in the justification (e.g., "The elephant bar is the tallest, so they weigh the most").

Model Proficient Response (Short Answer):
Prompt: "Your class tracked how many books each student read in a month. How would you use a spreadsheet to find the median number of books read? Explain your steps." Response: 1. List all the book counts in one column (e.g., A2:A25).
2. Sort the column from smallest to largest (so the middle value is easy to find).
3. Use the formula =MEDIAN(A2:A25) to calculate the median.
4. If there’s an even number of students, the median is the average of the two middle numbers (the formula does this automatically).
Why this works: It shows process (sorting), correct function (MEDIAN, not AVERAGE), and understanding (explaining how the median works).

4. Mistake Taxonomy

Mistake 1: The "Forgotten Equals Sign"
- Question: "Write a formula to add the numbers in cells B2 and B3." - Common wrong answer: B2+B3 - Why it loses credit: Spreadsheets treat B2+B3 as text, not a calculation. The = tells the spreadsheet, "This is a formula—do the math!" - Correct approach: - Start with =.
- Write =B2+B3 (or =SUM(B2:B3) for multiple cells).
- Pro tip: If the cell shows B2+B3 instead of a number, you forgot the =.

Mistake 2: The "Chart Mismatch"
- Question: "Create a chart to show how ice cream sales changed over the summer. Which chart type should you use?" - Common wrong answer: Pie chart (or "a circle chart").
- Why it loses credit: Pie charts show parts of a whole (e.g., % of sales by flavor), not trends over time. A line chart is better for showing change.
- Correct approach: - Use a line chart for trends (e.g., sales by date).
- Use a bar chart for comparisons (e.g., sales by flavor).
- Test it: If your chart’s x-axis is time (days, months), it’s probably a line chart.

Mistake 3: The "Absolute Reference Trap"
- Question: "You have a tax rate in cell D1 (7%). Write a formula in E2 to calculate the tax for the sale in B2. Then drag the formula down to E10." - Common wrong answer: =B2*D1 (dragged down becomes =B3*D2, =B4*D3, etc.).
- Why it loses credit: The tax rate moves when you drag the formula! D1 should stay fixed as D1 for all rows.
- Correct approach: - Use $D$1 to lock the cell: =B2*$D$1.
- Why it works: The $ tells the spreadsheet, "Don’t change this part when I drag the formula." - Grade 6 shortcut: Press F4 (Windows) or Cmd+T (Mac) after clicking D1 to add the $ automatically.

5. Connection Layer

1. Within Computer Science: Spreadsheets → Databases

2. Why it matters: A spreadsheet is like a simple database—both store data in tables, but databases (like those used by YouTube or Amazon) can handle millions of rows and answer complex questions (e.g., "Show me all users who bought lemonade on hot days in July"). Spreadsheets teach you how to structure data so it’s ready for bigger tools.

3. Across Subjects: Spreadsheets → Science (Experimental Design)

4. Why it matters: In science class, you might track plant growth under different light conditions. A spreadsheet lets you analyze the data (e.g., "Did plants grow faster under red or blue light?") the same way you’d analyze lemonade sales. The process is identical: collect data → organize → calculate → visualize.

5. Outside School: Spreadsheets → Fantasy Sports (or Video Game Stats)

6. Why it’s surprising: If you play Madden or NBA 2K, the "player stats" screen is basically a spreadsheet! Teams use spreadsheets to track real player performance (e.g., "How many points does LeBron average in the 4th quarter?"). Even Minecraft YouTubers use spreadsheets to track diamond mining rates or mob spawns.

6. The Stretch Question

"Your friend says, ‘A pie chart is the best way to show how my allowance is spent each month.’ Do you agree? What’s a better chart type—and why would it matter in real life?"

Pointer Toward the Answer:
- A pie chart can work for allowance (e.g., 50% snacks, 30% games, 20% savings), but it’s terrible for showing change over time (e.g., "Did I spend more on games this month than last?"). A bar chart would let you compare months side by side, and a line chart could show trends (e.g., "My snack spending is going up!").
- Real-life why: If you’re trying to save for a new bike, a pie chart won’t help you track progress. A line chart could show, "I saved $10 in January, $15 in February…" and motivate you to keep going. The right chart isn’t just about looking pretty—it’s about telling the story you need to hear.