Lesson Notes By Weeks and Term v5 - Grade 11

Lesson Video

This page supports the lesson note with a companion video and a short classroom-ready summary.

For class groups and homework, share this lesson page so learners also get the summary, objectives, and full lesson context.

Performance objectives

• Apply intermediate functions like IF, COUNTIF, SUMIF, and VLOOKUP.
• Create and customize various graphs (bar, pie, line, scatter).
• Interpret data presented in graphs.
• Use conditional formatting to highlight data.
• Understand how charts dynamically update with data.

Lesson summary

This week, we delve deeper into spreadsheet functionality, focusing on intermediate techniques and powerful data visualization using graphs. Spreadsheets are more than just tables; they're powerful tools for organizing, analyzing, and presenting data. This skill is crucial for understanding trends, making informed decisions, and communicating information effectively – skills highly valued in various fields, from business and finance to science and social studies. Imagine using spreadsheets to manage your small tuck shop's finances, analyzing crime statistics in your community, or even tracking your matric exam preparation progress.

Lesson notes

2.1 Intermediate Spreadsheet Functions Beyond basic calculations, spreadsheets offer powerful functions for more complex data manipulation.

IF Function: The `IF` function allows you to perform different calculations or display different values based on a condition.

The syntax is: `IF(condition, value_if_true, value_if_false)`.

Example:* A school wants to automatically assign bursaries to students based on their marks. If a student's average mark is above 75%, they receive a bursary.

The formula would be: `IF(A1>75, "Bursary Awarded", "No Bursary")` where A1 is the cell containing the student's average mark.

COUNTIF Function: The `COUNTIF` function counts the number of cells within a range that meet a given criteria.

The syntax is: `COUNTIF(range, criteria)`.

Example:* A municipality wants to know how many households in a specific area earn less than R5000 per month.

The formula would be: `COUNTIF(B2:B100, " 5")` `B2:B100` is the range containing the number of days absent. `">5"` is the criteria (we want to count the cells where the number of days absent is greater than 5).

Question 3: A municipality stores information about households, including their income level. They want to create a bar chart showing the number of households in each income category (Low, Medium, High). How would you approach this?

Solution: Create a summary table: In a separate part of the spreadsheet, create a table with two columns: "Income Category" (Low, Medium, High) and "Number of Households".

Use COUNTIF to populate the summary table: In the "Number of Households" column, use the `COUNTIF` function to count the number of households in each income category. For example, if the income levels are in column A (A2:A100), the formula for "Low" might be `=COUNTIF(A2:A100, "Low")`.

Create the bar chart: Select the summary table (Income Category and Number of Households) and insert a bar chart. Adjust the chart title, axis labels, and colors to make it clear and informative.

Question 4: A company has two spreadsheets. Spreadsheet 1 contains employee IDs and names. Spreadsheet 2 contains employee IDs and salaries. You want to create a single spreadsheet with employee names and salaries. How can you use VLOOKUP to achieve this?

Solution: Open both spreadsheets in the same spreadsheet program (e.g., Excel). Create a new column in Spreadsheet 1 for "Salary." In the first cell of the "Salary" column (e.g., C2 in Spreadsheet 1), enter the following formula: `=VLOOKUP(A2, '[Spreadsheet 2.xlsx]Sheet1'!A:B, 2, FALSE)` `A2` is the cell containing the Employee ID in Spreadsheet 1 (the lookup value). `'[Spreadsheet 2.xlsx]Sheet1'!A:B` is the table array in Spreadsheet 2 containing Employee IDs (column A) and Salaries (column B). Make sure to replace '[Spreadsheet 2.xlsx]Sheet1' with the actual file name and sheet name of Spreadsheet 2. `2` is the column index number (the salary is in the second column of the table array). `FALSE` ensures an exact match for the Employee ID. Drag the formula down to apply it to all employees in Spreadsheet

1. Independent Practice (Questions Only) A tuck shop records daily sales of different snacks. The spreadsheet has columns for Date, Snack, and Quantity Sold. Write a formula to calculate the total quantity of "Chips" sold in January. (Assume the Date column is formatted as DD/MM/YYYY) A farmer tracks the weight of oranges harvested each week. The spreadsheet has columns for Week Number and Orange Weight (in kg). Use conditional formatting to highlight the weeks where the orange weight was above the average weight for the entire period. A school stores student marks for different subjects. Create a pie chart showing the percentage of students who achieved each grade (A, B, C, D, E, F) in Mathematics. A local clinic tracks patient waiting times. The spreadsheet has columns for Date and Waiting Time (in minutes). Create a line graph showing the trend in waiting times over the past month. A research project collects data on two variables: Age and Income. Create a scatter plot to visualize the relationship between these two variables. A company has a spreadsheet of customer data. The spreadsheet contains columns for Customer ID, City, and Purchase Amount. Use a formula to calculate the total purchase amount for customers in Cape Town. A sports team tracks player performance. The spreadsheet has columns for Player Name, Goals Scored, and Assists. Use conditional formatting to highlight the players with the highest number of goals scored. Extend the previous question to create a summary table indicating the count of players in each performance category: "Top Performer" (Goals > 10), "Average Performer" (5 <= Goals <= 10), and "Needs Improvement" (Goals < 5). Then create a bar chart representing this summary data. A car dealership has a spreadsheet containing information about cars, including Make, Model, and Price.