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, VLOOKUP, and COUNTIF.
• Create and customize different graph types.
• Analyze data and identify trends from spreadsheets and graphs.
• Solve real-world problems using these tools.

Lesson summary

This week, we delve deeper into spreadsheet functionality, focusing on intermediate skills and the crucial art of data visualization through graphs. Understanding spreadsheets and graphs is essential in today's data-driven world. From tracking personal finances to analyzing business performance or even understanding national census data, these skills are directly applicable to various aspects of life in South Africa and beyond. A small business owner in Khayelitsha might use spreadsheets to manage stock and sales, while a farmer in Limpopo could use them to track crop yields and optimize fertilizer use.

Lesson notes

2.1 Intermediate Spreadsheet Functions IF Function: The `IF` function allows you to perform conditional calculations. Its syntax is `IF(logical_test, value_if_true, value_if_false)`. The `logical_test` is an expression that evaluates to TRUE or FALS

E. Example: Suppose a spaza shop offers a discount of 5% to customers who spend R200 or more. In a spreadsheet, you could use the `IF` function to calculate the discount: `=IF(B2>=200, B20.05, 0)`, where B2 is the total amount spent. This formula checks if the value in cell B2 is greater than or equal to

2

0

0. If it is, it calculates 5% of B2; otherwise, it returns 0 (no discount).

VLOOKUP Function: The `VLOOKUP` function searches for a value in the first column of a table array and returns a value in the same row from a column you specify. Its syntax is `VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`. `lookup_value`: The value to search for. `table_array`: The range of cells containing the table. `col_index_num`: The column number in the `table_array` from which to return a value. `[range_lookup]`: Optional. TRUE (approximate match) or FALSE (exact match). Usually set to FALSE for exact matches.

Example: A school maintains a lookup table of student IDs and their corresponding house affiliation. If the student ID is in cell A2, the table array is in cells D2:E100 (column D contains student IDs and column E contains house names), and you want to retrieve the house name, the formula would be: `=VLOOKUP(A2, D2:E100, 2, FALSE)`.

HLOOKUP Function: Similar to `VLOOKUP`, but searches horizontally (across the top row of a table) instead of vertically. Its syntax is `HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])`.

Example: Imagine a university fee schedule where fees for different courses are listed horizontally across a table. If the course code is in cell A2, the table array is in cells D1:H2 (row 1 contains course codes, row 2 contains fees), and you want to retrieve the fee for the course, the formula would be: `=HLOOKUP(A2, D1:H2, 2, FALSE)`.

COUNTIF Function: The `COUNTIF` function counts the number of cells within a range that meet a given criterion. Its syntax is `COUNTIF(range, criteria)`.

Example: To count the number of students who scored above 70% in a class, where the scores are in cells B2:B30, you could use the formula: `=COUNTIF(B2:B30, ">70")`.

SUMIF Function: The `SUMIF` function sums the values in a range that meet a given criterion. Its syntax is `SUMIF(range, criteria, [sum_range])`. If `sum_range` is omitted, the `range` is summed.

Example: A fruit vendor wants to calculate the total sales for apples only. Column A contains the fruit names, and column B contains the sales amounts.

The formula would be: `=SUMIF(A2:A100, "apples", B2:B100)`.

AVERAGEIF Function: The `AVERAGEIF` function calculates the average of the values in a range that meet a given criterion. Its syntax is `AVERAGEIF(range, criteria, [average_range])`. If `average_range` is omitted, the `range` is averaged.

Example: A taxi company wants to find the average distance travelled by taxis on weekends only. Column A contains the day of the week, and column B contains the distances travelled.

The formula would be: `=AVERAGEIF(A2:A100, "Saturday", B2:B100)` or `=AVERAGEIF(A2:A100, "Sunday", B2:B100)` (You would need to calculate each day seperately). 2.2 Graphs and Charts Graphs are visual representations of data. Choosing the right graph type is crucial for effectively communicating your message.

Column Chart: Compares values across categories using vertical bars. Useful for showing changes in data over time or comparing different items.

Example: Comparing the number of learners enrolled in different schools in a district.

Bar Chart: Similar to a column chart but uses horizontal bars. Useful when category labels are long.

Example: Comparing the sales of different types of snacks in a tuck shop.

Line Chart: Shows trends over time. Data points are connected by lines.

Example: Tracking the monthly rainfall in a particular region.

Pie Chart: Shows the proportion of each category in relation to the whole. Useful for showing percentages or relative sizes.

Example: Displaying the ethnic makeup of a South African community.

Scatter Chart: Shows the relationship between two sets of numerical data.

Example: Examining the correlation between study hours and exam scores.

Customizing Graphs: Chart Title: A clear and concise title explaining what the graph represents.

Axis Labels: Labels for the x-axis and y-axis, indicating what each axis represents and their units (e.g., "Month", "Rainfall (mm)").

Data Labels: Display values directly on the data points (e.g., showing the exact enrolment number on each bar in a column chart).

Legend: Explains what each color or pattern represents in the graph. Guided Practice (With Solutions)

Question 1: A school wants to award bursaries to learners based on their average mark.