Lesson Notes By Weeks and Term v5 - Grade 12

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

• Create and use advanced formulas (nested functions, logical operators).
• Apply advanced filtering and sorting techniques.
• Develop and apply PivotTables and PivotCharts.
• Use data validation to ensure data integrity.
• Import and export data from various sources.

Lesson summary

This week, we delve into advanced spreadsheet techniques and data analysis, building on your existing knowledge to develop practical solutions for real-world problems. Spreadsheet software, such as Microsoft Excel, Google Sheets, or LibreOffice Calc, are powerful tools used extensively in businesses, government, and even personal finance. Understanding how to use these tools effectively is a vital skill that will enhance your employability and problem-solving abilities in many aspects of life, from budgeting for a university education to analysing business data for a start-up idea.

Lesson notes

2.1 Advanced Formulas Beyond simple arithmetic, advanced formulas leverage functions like `IF`, `AND`, `OR`, `VLOOKUP`, `HLOOKUP`, `INDEX`, `MATCH`, `SUMIFS`, `COUNTIFS`, and array formulas to perform complex calculations based on multiple criteria. `IF` Function: Performs a logical test and returns one value if the condition is TRUE and another value if the condition is FALS

E. Syntax: `IF(logical_test, value_if_true, value_if_false)`

Example: Calculating a discount based on purchase amount. If the purchase amount is over R1000, give a 5% discount; otherwise, no discount. `=IF(A1>1000, A10.05, 0)` where A1 contains the purchase amount. `AND`, `OR`, `NOT` Functions: These logical functions allow you to combine multiple conditions. `AND(logical1, logical2, ...)`: Returns TRUE if all conditions are TRUE. `OR(logical1, logical2, ...)`: Returns TRUE if at least one condition is TRUE. `NOT(logical)`: Reverses the value of its argument. Returns TRUE if the argument is FALSE, and FALSE if the argument is TRU

E. Example: Granting a loan only if the applicant's age is over 25 AND their income is greater than R20,000. `AND(A1>25, B1>20000)` where A1 is age and B1 is income. `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.

Syntax: `VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`

Example: Looking up the price of a product based on its product code. `=VLOOKUP(A1, B1:C100, 2, FALSE)` where A1 contains the product code, B1:C100 is the table containing product codes and prices (column 2), and FALSE ensures an exact match. Imagine a retail store in Cape Town using this function to automatically display the price of an item when the cashier scans the barcode. `HLOOKUP` Function: Similar to VLOOKUP, but searches horizontally in the first row of a table array.

Syntax: `HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])` `INDEX` and `MATCH` Functions: `INDEX` returns a value from a specified row and column in a range or array. `MATCH` returns the relative position of an item in an array that matches a specified value. They are often used together to provide more flexible lookups than `VLOOKUP` or `HLOOKUP`.

Example: `=INDEX(C1:C100, MATCH(A1, B1:B100, 0))` where A1 contains the lookup value, B1:B100 contains the lookup array, and C1:C100 contains the values to return. `SUMIFS` and `COUNTIFS` Functions: These functions allow you to sum or count values in a range that meet multiple criteria. `SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)` `COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)`

Example: Calculating the total sales for a specific product in a specific region. `=SUMIFS(C1:C100, A1:A100, "Product A", B1:B100, "Gauteng")` where C1:C100 is the sales amount, A1:A100 is the product name, and B1:B100 is the region. This is invaluable for a company that operates across multiple provinces in South Africa.

Array Formulas: Perform calculations on multiple values at once. They are entered by pressing Ctrl+Shift+Enter.

Example: Calculating the sum of the squares of a range of numbers. `{=SUM(A1:A10^2)}` (entered with Ctrl+Shift+Enter) 2.2 Advanced Filtering and Sorting Custom Filters: Allows you to specify complex criteria for filtering data beyond simple "equal to" or "greater than" conditions. You can use wildcards (, ?) and logical operators to create sophisticated filters.

Example: Filtering a list of customers to show only those whose names start with "M" and are located in Durban.

Multiple Level Sorting: Sort data based on multiple columns, with each column having its own sorting order (ascending or descending).

Example: Sorting a list of students first by their grade (descending) and then by their last name (ascending). This would be helpful for generating a class list ordered by academic performance. 2.3 PivotTables and PivotCharts PivotTables are interactive tables that summarize and analyze large amounts of data. They allow you to easily reorganize and aggregate data by dragging and dropping fields into different areas of the table (Rows, Columns, Values, Filters). PivotCharts are visual representations of PivotTable data.

Creating a PivotTable: Select your data range, then go to Insert > PivotTable.

Adding Fields: Drag fields from the PivotTable Fields pane to the Rows, Columns, Values, and Filters areas to define the structure of your PivotTable.

Summarizing Data: Choose the type of calculation to perform on the values (e.g., Sum, Average, Count, Max, Min).

Example: A school principal might use a PivotTable to analyze student performance across different subjects, identifying areas where students are struggling and where they excel. They could filter by grade level, race or gender to identify achievement gaps and allocate resources where most needed.