Term: 3rd Term

Week: 5

Class: Senior Secondary School 1

Age: 15 years

Duration: 40 minutes of 2 periods each

Date:

Subject: Data Processing

Topic:- Spreadsheet II

SPECIFIC OBJECTIVES: At the end of the lesson, pupils should be able to

Describe how to perform some calculations in excel
Highlight some popular errors in excel

INSTRUCTIONAL TECHNIQUES: Identification, explanation, questions and answers, demonstration, videos from source

INSTRUCTIONAL MATERIALS: Videos, loud speaker, pictures, Data Processing for senior Secondary Education by Hiit Plc, WAPB Computer Studies for Senior Secondary I by Adekunle et al, On-line Materials.

INSTRUCTIONAL PROCEDURES

PERIOD 1-2

PRESENTATION	TEACHER’S ACTIVITY	STUDENT’S ACTIVITY
STEP 1 INTRODUCTION	The teacher reviews the previous lesson on Spreadsheet	Students pay attention
STEP 2 EXPLANATION	He describes how to perform calculations in excel	Students pay attention and participates
STEP 3 DEMONSTRATION	He explains some errors that could occur in Excel	Students pay attention and participate
STEP 4 NOTE TAKING	The teacher writes a summarized note on the board	The students copy the note in their books

NOTE

Working with Excel worksheet

Recall that a cell can take short text (label), number and formula. So when working with Excel, you are expected to type in any of the above in the cells. The Excel sheet below will be used for our practical example.

Calculating in Excel

To do any form of calculation, a user is required to put a formula together or by using the pre-defined functions available in Excel.

To use any of the available functions, the following information should be taken into consideration:

The cell that you want to store the result of your calculation must be made active
Any formula or function to be inserted must be preceded by an equality sign (=) followed by desired argument
Press Enter key when done to see the result of your formula

A formula using a function name is of the form

= funct_name (arg1, arg2 , …, argN) ,

where N >= 1 and funct_name is the name of the function e.g. SUM, AVERAGE, COUNT, MIN, MAX etc.

Addition in Excel

Consider the Excel sheet above, to calculate the CA score for the first student (ESTHER) on the table:

Type any of the following formulas in cell D2

=B2+ C2
=SUM(B2,C2)
=SUM(B2:C2)

To calculate the Total score for TOBILOBA, use the formula:

=D4 + E4
=SUM(D4,E4)
=SUM(D4:E4)

Note: Total = CA + Exam

Average in Excel

Assuming the Total score for all students have been calculated, how do you calculate the Average of all the Total scores?

Note that the Total score for each student is calculated in column F, cell F2 for the first student up to cell F9 for the last student. To get the average of the Total scores, use the formula below:

=(F2+F3+F4+F5+F6+F7+F8+F9)/8
=AVERAGE(F2,F3,F4,F5,F6,F7,F8,F9)
=AVERAGE(F2:F9)

Minimum and Maximum in Excel

To calculate the lowest Exam score, use the following formula:

=MIN(E2,E3,E4, E5,E6,E7,E8,E9)
=MIN(E2:E9)

To calculate the highest Exam score, use the following formula:

=MAX(E2,E3,E4, E5,E6,E7,E8,E9)
=MAX(E2:E9)

Grading in Excel

Consider the table above, If we are to grade according to a particular condition say, if a student’s Total score is 50 and above then his/her grade is “PASS” and if below 50, the grade is “FAIL”. How do you use Excel to achieve this?

To calculate the grade, MS Excel provides the IF function which has the syntax:

=IF(logical_test, value_if_true, value_if_false) , where:

Logical_test is what you want to test for i.e. is Total score is greater than or equal to 50?
Value_if_true is what should happen if the ‘logical_test’ is true i.e. “PASS”.
Value_if_false is what should happen if the ‘logical_test’ is false i.e. “FAIL”.

So how do you calculate the grade for the last student on the list? i.e. JOSHUA

It is assumed that the total score has been calculated for JOSHUA, so in cell G9, type the following formula:

=IF(F9>=50, “PASS”,”FAIL”)

F9 is the cell where the Total score for JOSHUA is located, the ‘>=’ is the conditional operator to test if the value in cell F9 is greater than or equal to 50. If the test is carried out and it amount to true, then “PASS” will be inserted into cell G9 else ‘FAIL’.

DO IT YOURSELF!

What if the conditions are:

Total score is 75 and above, then grade is “ABOVE AVERAGE”, 50 to 74, the grade is “AVERAGE” and below 50 , the grade is “BELOW AVERAGE”. Write the IF functions to achieve this. Try this on your own!

Formula errors in Excel

##### error:when your cell contains this error, the column isn’t wide enough to display the value. The solution is to increase the width of the cell by double-clicking the right border of the cell (at the top of the cell column) or clicking and dragging to the right.
#NAME? error:occurs when Excel does not recognize the text in a formula. For example typing the formula =SU(A1:A3), Excel can’t recognise the function name ‘SU’. So the solution is to change the ‘SU’ to ‘SUM’ which is a correct function name for addition.
#VALUE! Error: occurs when a formula has the wrong type of argument. For example cell A1 has 4, cell A2 has 5 and cell A3 has ‘Hi’. If you try to add the three using the formula =SUM(A1:A3), Excel displays #VALUE! Error because the value in cell A3 is a text and not a number. The solution is to change the value in cell A3 to a number.
#DIV/0! Error: occurs when a formula tries to divide a number by zero (0) or an empty cell. The solution is to change the dividing value to a value not equal to zero (0)

EVALUATION: 1. Describe how to carry out the following calculations in excel

a. addition

b. average

c. minimum

d. maximum

2. Describe how to carry out grading in excel

3. What are some of the formula errors in excel and when do they occur?

CLASSWORK: As in evaluation

CONCLUSION: The teacher commends the students positively

Lesson Notes By Weeks and Term - Senior Secondary 1