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, and VLOOKUP.
• Create various graphs: bar, line, pie, and scatter plots.
• Format graphs for clear communication.
• Perform 'what-if' analysis using scenarios.
• Import and export data from formats like CSV.

Lesson summary

This week, we delve into intermediate spreadsheet skills and graph creation using software like MS Excel, Google Sheets, or LibreOffice Calc. Spreadsheets are incredibly powerful tools for organizing, analyzing, and visualizing data. In the South African context, understanding spreadsheets is vital for managing personal finances, tracking business expenses, analyzing market trends, and even understanding government budgets and statistics. Imagine a small spaza shop owner using a spreadsheet to track their inventory and sales to make informed decisions about what to stock and when to order.

Lesson notes

2.1 Intermediate Spreadsheet Functions: `IF` Function: The `IF` function allows you to perform different calculations based on a condition. Its syntax is `IF(logical_test, value_if_true, value_if_false)`.

Example: Imagine a small business granting a discount to customers who spend more than R

5

0

0. In cell C2, we could have: `=IF(B2>500, A20.9, A2)`, where A2 is the price of the item and B2 is the quantity purchased.

This formula says: "If the quantity (B2) is greater than 500, calculate the discounted price (A2 * 0.9), otherwise, display the original price (A2)." `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: A school wants to know how many students scored above 70% in a test.

Assuming the scores are in the range B2:B100, the formula would be: `=COUNTIF(B2:B100, ">70")`. This counts the number of scores in the range that are greater than 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])`.

Example: A retail store wants to calculate the total sales for a specific product. Column A contains the product names, Column B contains the sales amount. The formula `=SUMIF(A2:A100, "Shoes", B2:B100)` will sum the sales amounts (B2:B100) only where the product name in column A is "Shoes". `AVERAGEIF` Function: The `AVERAGEIF` function calculates the average of cells in a range that meet a given criterion. Its syntax is `AVERAGEIF(range, criteria, [average_range])`.

Example: You want to calculate the average salary of female employees in a company. Column A contains gender ("Male" or "Female") and Column B contains salary. `=AVERAGEIF(A2:A100, "Female", B2:B100)` will calculate the average salary of all female employees. `VLOOKUP` Function: The `VLOOKUP` function searches for a value in the first column of a table 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])`.

Example: A school uses VLOOKUP to automatically populate a student's grade based on their percentage. They have a table (e.g., in Sheet2) with percentage ranges and corresponding grades. The lookup value is the student's percentage, the table array is the table in Sheet2, and the column index number is the column containing the grades. The `range_lookup` argument is usually set to `TRUE` for approximate match (percentage ranges). `=VLOOKUP(A2, Sheet2!A1:B10, 2, TRUE)` where A2 is the percentage. 2.2 Graph Types and Their Uses: Bar Graphs: Used to compare discrete categories. Suitable for showing sales figures for different products or population sizes in different provinces.

Line Graphs: Used to show trends over time. Ideal for visualizing stock prices, temperature changes, or population growth.

Pie Charts: Used to show proportions of a whole. Effective for representing market share, budget allocation, or the composition of a population group.

Scatter Plots: Used to show the relationship between two variables. Useful for identifying correlations between study hours and exam scores, or between advertising spend and sales revenue. 2.3 Graph Formatting and Customization: Titles and Labels: Clear titles and axis labels are essential for understanding the graph.

Legends: Legends explain what each data series represents.

Axis Scales: Appropriate axis scales prevent distortion and ensure accurate representation.

Data Labels: Data labels display the exact value for each data point.

Color and Font Choices: Use colors and fonts that are easy to read and visually appealing. 2.4 "What-If" Analysis: Scenario Manager: Allows you to define different sets of input values (scenarios) and see how they affect the results. For example, you could create scenarios for best-case, worst-case, and most likely sales projections.

Goal Seek: Allows you to find the input value needed to achieve a specific target output. For example, you could use Goal Seek to determine the number of units you need to sell to reach a certain profit target. 2.5 Data Import and Export: CSV (Comma Separated Values): A simple text format where values are separated by commas.

TXT (Text File): A basic text format that can be easily opened and edited.

Importing: Spreadsheets can import data from CSV and TXT files, allowing you to combine data from different sources.

Exporting: Spreadsheets can export data to CSV and TXT files for use in other applications. Guided Practice (With Solutions)

Question 1: A school needs to award bursaries to students whose average mark is above 75%. Write a formula to display "Bursary Awarded" if the average mark (in cell B2) is greater than 75, and "No Bursary" otherwise.

Solution: `=IF(B2>75, "Bursary Awarded", "No Bursary")` Explanation: The `IF` function checks if the value in B2 is greater than 75.