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

• Use intermediate functions like IF, SUMIF, and VLOOKUP.
• Create and customize various graph types.
• Analyze data to identify trends and patterns.
• Apply conditional formatting to highlight data.
• Import data from external sources.

Lesson summary

This week, we delve deeper into spreadsheets, focusing on intermediate functions and effective graph creation. Spreadsheets are not just for accountants! They are powerful tools for analyzing data and making informed decisions in various aspects of life, from managing a small business to understanding national economic trends. In South Africa, with its diverse economic landscape, being able to analyze data presented in spreadsheets is a crucial skill for entrepreneurs, employees, and even informed citizens.

Lesson notes

2. 1. Intermediate Spreadsheet Functions 2.1.

1. The `IF` Function: The `IF` function allows you to perform different calculations based on whether a condition is true or false.

Its syntax is: `=IF(condition, value_if_true, value_if_false)` condition: A logical expression that evaluates to TRUE or FALSE (e.g., `A1 > 10`, `B2 = "Yes"`). value_if_true: The value to return if the condition is TRUE. value_if_false: The value to return if the condition is FALS

E. Example: Suppose you have a spreadsheet tracking student scores in a test. Column A contains the student names, and column B contains their scores. You want to create a new column C that indicates whether each student passed or failed, with a passing score of

5

0. In cell C2, you would enter: `=IF(B2>=50, "Pass", "Fail")`. This means "If the score in B2 is greater than or equal to 50, then display "Pass", otherwise display "Fail"". You can then copy this formula down column C to apply it to all students. 2.1.

2. The `SUMIF` Function: The `SUMIF` function sums values in a range that meet a specified criterion.

Its syntax is: `=SUMIF(range, criterion, sum_range)` range: The range of cells to evaluate the criterion against. criterion: The condition that determines which cells will be summed (e.g., `">100"`, `"Johannesburg"`). sum_range: The range of cells to sum. If omitted, the `range` is summed.

Example: Imagine you're tracking sales data for different regions in South Africa. Column A contains the region names (e.g., "Gauteng", "Western Cape", "KwaZulu-Natal"), and column B contains the sales figures. You want to calculate the total sales for Gauteng. In a separate cell, you would enter: `=SUMIF(A:A, "Gauteng", B:B)`. This means "Sum the values in column B where the corresponding value in column A is equal to "Gauteng"". 2.1.

3. The `AVERAGEIF` Function: Similar to `SUMIF`, `AVERAGEIF` calculates the average of values that meet a specified criterion.

Its syntax is: `=AVERAGEIF(range, criterion, average_range)` range: The range of cells to evaluate the criterion against. criterion: The condition that determines which cells will be averaged. average_range: The range of cells to average. If omitted, the `range` is averaged.

Example: Using the same sales data as above, you want to calculate the average sales for regions with sales figures over R100,

0

0

0. You would enter: `=AVERAGEIF(B:B, ">100000", B:B)`. This means "Calculate the average of the values in column B where the value is greater than 100000". 2.1.

4. The `COUNTIF` Function: The `COUNTIF` function counts the number of cells within a range that meet a specified criterion.

Its syntax is: `=COUNTIF(range, criterion)` range: The range of cells to evaluate. criterion: The condition that determines which cells will be counted (e.g., `"Yes"`, `" = 50).

You would enter: `=COUNTIF(B:B, ">=50")`. This means "Count the number of cells in column B where the value is greater than or equal to 50". 2.1.

5. The `VLOOKUP` Function: The `VLOOKUP` function searches for a value in the first column of a table and returns a value from a specified column in the same row.

Its syntax is: `=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])` lookup_value: The value you want to search for. table_array: The range of cells that makes up the table where you want to search (including the lookup column and the column containing the return value). Always use absolute cell referencing (e.g., $A$1:$C$10) to prevent the range from changing when copying the formula. col_index_num: The column number in the `table_array` from which to return a value. The first column is 1. [range_lookup]: Optional. TRUE (or omitted) for approximate match (the first column in the table must be sorted in ascending order). FALSE for exact match. Always use FALSE for this level unless specifically instructed otherwise!

Example: You have a table listing product codes and their corresponding prices. Column A contains the product codes, and column B contains the prices. You want to create a formula that automatically retrieves the price for a given product code entered in cell D

1. The table is in the range A1:B

1

0. In cell D2, you would enter: `=VLOOKUP(D1, $A$1:$B$10, 2, FALSE)`. This means "Look for the value in cell D1 (the product code) in the first column of the table A1:B

1

0. If an exact match is found, return the value from the 2nd column (the price) of the same row". The `FALSE` ensures an exact match is found, avoiding errors. 2.

2. Creating and Customizing Graphs Spreadsheets offer a variety of graph types to visualize data, each suited for different purposes: Column Charts: Compare values across different categories. Ideal for showing sales figures for different regions.

Bar Charts: Similar to column charts, but with horizontal bars. Useful when category labels are long.

Line Charts: Show trends over time. Great for displaying monthly rainfall data or stock market fluctuations.

Pie Charts: Display the proportion of each category to the whole.