Topicm1647b7b84d3b82d7_1528449000663_0Topic

Creating nested formulas

Levelm1647b7b84d3b82d7_1528449084556_0Level

Second

Core curriculumm1647b7b84d3b82d7_1528449076687_0Core curriculum

Grade VII and VIII

II. Programming and solving problems by using the computer and other digital devices. Using computer applications the student:

3) creates documents and presentations, including those in a cloud in order to solve problems and create papers from various fields (subjects), adjusts the format and the appearance of the research papers to their content/purpose and demonstrates the following skills:

c) performing calculations specified in the curriculum of various subjects within the scope of primary school and useful in everyday life and implementing selected algorithms in a spreadsheet: entering data in a worksheet table, using basic functions, using relative, absolute and mixed references, presenting data in the form of various charts, ordering and filtering data.

Timingm1647b7b84d3b82d7_1528449068082_0Timing

45 minutes

General objectivem1647b7b84d3b82d7_1528449523725_0General objective

To use a spreadsheet to solve tasks.

Specific objectivesm1647b7b84d3b82d7_1528449552113_0Specific objectives

1. To create nested formulas.

Learning outcomesm1647b7b84d3b82d7_1528450430307_0Learning outcomes

The student:

- uses the result of a built‑in functionbuilt‑in functionbuilt‑in function as an argumentargumentargument of other functionfunctionfunction.

Methodsm1647b7b84d3b82d7_1528449534267_0Methods

1. Learning through observation.

2. Practical use of knowledge.

Forms of workm1647b7b84d3b82d7_1528449514617_0Forms of work

1. Individual work.

2. Class work.

Lesson stages

Introductionm1647b7b84d3b82d7_1528450127855_0Introduction

Students resemble the syntax of functions in MS Excel

The syntax of the functionfunctionfunction:

= FUNCTION.NAME(ARG_1, ARG_2, … , ARG_n)

Procedurem1647b7b84d3b82d7_1528446435040_0Procedure

Task
Let's colour cells (A1:E1) yellow, and (A2:E2) - green and enter random numbers ranging from 1 to 10 in them. In cell A3 let's display information the average (arithmetic mean) of which cells is greater.
Let's do the task in 3 stages:

Stage I – let's use the auxiliary cells F1 and F2 and calculate in them the arithmetic mean of the yellow and green cells using the AVERAGE functionfunctionfunction. Using the values of these cells, let's calculate in cell A3, with the use of the IF functionIF functionIF function, which arithmetic mean is greater.
Stage II – based on the formulas in cells F1, F2, A3, let's do the same task in cell B3 by nesting the IF functionIF functionIF function without using the auxiliary cells F1 and F2.
Stage III – let's develop the nested functionnested functionnested function so that it operates correctly also when the arithmetic means are equal.

Definition
A nested function is a function that uses other function as its argument.m1647b7b84d3b82d7_1527752263647_0A nested function is a function that uses other function as its argument.

Task
In the documentation of the programme find the information on how many levels of functions you can nest in a formula.

Task
If we want to test for N different conditions with the use the IF functionIF functionIF function, how many IF functions must we use?

Task
In a new column, grade 5 students whose test scores are: 14, 43, 24,18 and 9, using the following scoring:

- very good – 50 - 41 points,
- good – 40 - 31 points,
- satisfactory – 30 - 21 points,
- pass – 20 - 11 points,
- fail – 10 - 1 points.

How many conditions should be tested? How many IF functions will you use? Is the order of testing conditions relevant? Before doing the task draw the operational chart of the nested IF functionIF functionIF function.

Lesson summarym1647b7b84d3b82d7_1528450119332_0Lesson summary

We use function nesting when:
- we want to perform calculations faster without using interim calculations,
- the function we need is not available in Excel, and the desired effect can be achieved using the combined operation of other existing functions.m1647b7b84d3b82d7_1527752256679_0We use function nesting when:
- we want to perform calculations faster without using interim calculations,
- the function we need is not available in Excel, and the desired effect can be achieved using the combined operation of other existing functions.

Selected words and expressions used in the lesson plan

argumentargumentargument

built‑in functionbuilt‑in functionbuilt‑in function

functionfunctionfunction

IF functionIF functionIF function

multiple criteriamultiple criteriamultiple criteria

nested functionnested functionnested function