Topicm700275a2b66415df_1528449000663_0Topic

Creating formulas using testing for conditions

Levelm700275a2b66415df_1528449084556_0Level

Second

Core curriculumm700275a2b66415df_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.

Timingm700275a2b66415df_1528449068082_0Timing

45 minutes

General objectivem700275a2b66415df_1528449523725_0General objective

Designing spreadsheets.

Specific objectivesm700275a2b66415df_1528449552113_0Specific objectives

Using conditional functions in a spreadsheet.

Learning outcomesm700275a2b66415df_1528450430307_0Learning outcomes

The student:

- identifies conditional functions,

- uses conditional functions accordingly.

Methodsm700275a2b66415df_1528449534267_0Methods

1. Discussion.

2. Learning through observation.

Forms of workm700275a2b66415df_1528449514617_0Forms of work

1. Individual work.

2. Class work.

Lesson stages

Introductionm700275a2b66415df_1528450127855_0Introduction

Before the lesson the students revise the information about formulas and functions in a spreadsheet. The teacher initiates a short discussion:

- What is a formulaformulaformula in a spreadsheet?
- What functions have you already used in a spreadsheet?
- What can a formula or functionfunctionfunction in a spreadsheet refer to?
- What is an argument of a function?
- What comparison operators can be used in a spreadsheet?

Procedurem700275a2b66415df_1528446435040_0Procedure

The IF functionfunctionfunction allows you to make logical comparisons between the value of a cell and the expected value. When you use the IF function, you must specify three argumentsargumentsarguments:

[Interactive graphics]

Colloquially, the operation of the IF function could be described as follows: IF (the expression is true, then perform the specified operation, otherwise perform another operation). The IF function is also called a conditional functionconditional functionconditional function. It can be inserted with the function wizard or by using the keyboard. Conditional functions can be nested, which will be explained later.

[Illustration 1]

[Illustration 2]

Task 1

Download the file fruits.xls. By using the IF function, create a conditional statement which will return one of the two values in the column State: “realized” (when a given person has exceeded the limit) or “unrealized” (when a person has not exceeded the limit). In the column Surplus, insert the value of the surplus for people who have exceeded the limit. For people who have not reached the limit, insert “0” in the column Surplus.The If function can return the value of mathematical calculations. In the boxes Value_if_true and Value_if_false, you can enter mathematical expressions calculating values.m700275a2b66415df_1527752256679_0Download the file fruits.xls. By using the IF function, create a conditional statement which will return one of the two values in the column State: “realized” (when a given person has exceeded the limit) or “unrealized” (when a person has not exceeded the limit). In the column Surplus, insert the value of the surplus for people who have exceeded the limit. For people who have not reached the limit, insert “0” in the column Surplus.The If function can return the value of mathematical calculations. In the boxes Value_if_true and Value_if_false, you can enter mathematical expressions calculating values.

[Attachment 1]

Task 2

Calculate a bonus for the employees by awarding the bonus of 10% of the income for persons whose income is less than 5000 and of 5% of the income for persons whose income is more than 5000.

[Illustration 3]

The COUNTIF function enables to count the number of cells that meet a specified criterioncriterioncriterion. Practically, the syntax of this instructioninstructioninstruction can be translated as follows:

[Interactive graphics 2]

The text‑based criteria of searching for valuesvaluesvalues counted by the function are not case sensitive. Wildcard characters – the question mark (?) and asterisk (*) – which match any single character or any sequence of characters, respectively – can be used in criteria.

[Illustration 4]

Task 3

In the fruits.xls spreadsheet, count how many persons were picking the particular fruits and how many of them have reached their limits.

The SUMIF functionfunctionfunction is used to sum the valuesvaluesvalues in a range that meet criteria specified by the user. The criteria argument may contain numbers, text, expressions and cell references.

[Interactive graphics 3]

[Illustration 5]

Task 4

In the fruits.xls spreadsheet, count how many kilograms of fruits have been picked in total by all persons.

Lesson summarym700275a2b66415df_1528450119332_0Lesson summary

The IF function is one of the most popular functions in Excel. The logical functions in Excel enable browsing and analysing data. Every expanded formula searching for data in a spreadsheet performs various tests for assumptions or comparisons with required criteria. Therefore you can say that “the spreadsheet is intelligent”.m700275a2b66415df_1527752263647_0The IF function is one of the most popular functions in Excel. The logical functions in Excel enable browsing and analysing data. Every expanded formula searching for data in a spreadsheet performs various tests for assumptions or comparisons with required criteria. Therefore you can say that “the spreadsheet is intelligent”.

Selected words and expressions used in the lesson plan

argumentsargumentsarguments

conditional functionconditional functionconditional function

criterioncriterioncriterion

formulaformulaformula

functionfunctionfunction

instructioninstructioninstruction

mathematical expressionsmathematical expressionsmathematical expressions

valuesvaluesvalues