Créer une présentation
Télécharger la présentation

Télécharger la présentation
## e-homework Sept 2014

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

**e-homework Sept 2014**Year 11 ICT emeka1cc@yahoo.com**Do Now**(a) Write one cell on the spreadsheet that contains numeric data. …………………………………………………………………………………………………………... [1] (b) How many columns are there in this part of the spreadsheet? .............. [1] (c) Write down the formula which should be in cell E4. …………………………………………………………………………………………………………... [1] (d) What type of alignment is being used in cell B4? …….………………………….. [1] • The owner of the car showroom wants to calculate the total profit of these four cars. What formula should he type in cell E9? ………………………………………….…………………………………………………………… [1] • If the formula =MAX(E4:E7) is entered into cell E8, what result would be displayed? ………………………………………………………………………………………………………. [1] • The owner of the car showroom wants to sort the data in descending order of Registration number. What is the selling price of the car that will be on top after the sorting? ……………………………………………………………………………………………………… [1]**Click here to access the starter worksheet**Starter Activity**Topic**Data Analysis – Nested IF, interrogation & summarizing data with chart Grade C/B/A/A***Lesson Objectives**To know some common functions in spreadsheet (Grd C) Understand the use of some spreadsheet functions (Grd B) Use spreadsheet application to perform some clerical tasks following a set worksheet (Grd A/A*)**Keywords**• Function • Formula • Replication • Vlookup • IF • Nested IF • Countif • Averageif • Sumif • Filter • Sort • Interrogate • Chart • Named range/cell • Format • Hide • Wildcard**Why Spreadsheet Functions?**• The knowledge and application of spreadsheet is versatile and it includes • Business book keeping • Computer programming • Personal accounting • Banking and Finance**Main activity**A set of practical tasks are given in the worksheet and the required support files are included in the homework folder The support files must be downloaded alongside the worksheets from the homework folder for use Your are to carry out the Do Now activity separately as well as the Starter activity You will produce hardcopies of the DO Now and Starter activity worksheets after answering the questions The main task sheet for the practical work would require the support files which were included in the homework folder Following the tasks in the worksheet perform the accompanying tasks using the appropriate support files**Introduction**Functions and formulas are used to perform calculations on numeric data sets. Functions are predefined rules/formulas in the spreadsheet application. Sometimes the user may choose to define or create his/her own formula depending on the task**Lesson Objective 1 -To know some common functions in**spreadsheet (Grd C) It is easier to use function to carry out calculations by supplying the required argument (data) Some of these functions include: COUNT, COUNTA, LOOKUP, AVERAGE, PRODUCT, INT,MIN, MAX, etc Research these functions using spreadsheet software help tool or the internet**L.O. 2 – SUM {syntax =SUM(selected range of cells}**The SUM function is used to perform addition of selected range of cells containing numeric values. To use it follow these steps: • Click the cell in the worksheet where the answer will appear • Click the insert function button on the formula bar • Select SUM from the insert function dialog window and click OK • Highlight the range of cells whose values should be added • Click OK**L.O. 2 – COUNTIF {syntax =COUNTIF(range, criteria}**The COUNTIF function is used to count the values in a range of cells that satisfy a given condition. To use it follow these steps: • Click the cell in the worksheet where the answer will appear • Click the insert function button on the formula bar • Select COUNTIF from the insert function dialog window and click OK • In the range bar highlight the range of cells whose values are to be counted that must contain the criterion • Then click the criteria bar and type the argument (i.e. the given value in the range to be counted) • Click OK**L.O. 2 – SUMIF {syntax =SUMIF(range, criteria, sum_range}**The SUMIF function is used to add the values in a numeric range of cells that correspond to the satisfying criterion in a given range. To use it follow these steps: • Click the cell in the worksheet where the answer will appear • Click the insert function button on the formula bar • Select SUMIF from the insert function dialog window and click OK • In the range bar highlight the cells that contain the referenced criterion for the range argument (i.e. values). • Click the criteria bar and type in the criterion (i.e. one of the values in the range) for which you are summing over • Then click the sum_range bar and highlight the sum_range(cells in the worksheet containing the actual values to be added) • Click OK**L. O. 3Use spreadsheet application to perform some clerical**tasks following a set worksheet (Grd A/A*) • Research the following spreadsheet functions using spreadsheet help tool • AVERAGEIF • VLOOKUP • ROUND • Naming a cell or group of cells • Carry out the tasks on the worksheet 3 included in the folder. {click on the link to open the worksheet} • The supporting files are included in this homework folder • Your print outs should be handed in on resumption**AFL Time**• LO1: I can list some common spreadsheet functions • LO2: I understand how to use the functions accurately • LO3: I am able to use some spreadsheet functions to perform the given tasks in the worksheet • Please tick the smiley that best describes your feeling for each of the learning objective:**Do Now**• Which of these functions will produce the result in cell B8? i) =COUNTIF(B4:B8, B4) ii) =COUNTIF(B4:B8, “TRU 234”) iii) =COUNTIF(B4:B8, 4)[1] (b) Write down the formula which should be in cell E8. ………………………………………………………………………………………………………….... [2] • What type of format was applied to the values in the range C4:D8? …………………………………………………………………..…….………………………….. [1] • The owner of the car showroom wants to calculate the total profit of the cars. What formula should he type in cell E9? …………………………………………………………………..…….………………………….. [1] • If the formula =MAX(E4:E8) is entered into cell E8, what result would be displayed? ………………………………………………………………………………………………………. [2] • Pie chart can be used to summarize information graphically? i. TRUE ii. FALSE**Starter**• Research the following spreadsheet functions using spreadsheet application help tool • Wildcards • Bar chart • Hiding rows or columns • Interrogation • Chart legend**Main/Student activity**Carry out the tasks on the worksheet 4 included in the folder. {click on the above link to open the worksheet} The supporting file is the work you saved in task 16 of worksheet 3 Your print outs should be handed in on resumption**AFL Time**• LO3a: I am able to interrogate the data • LO3b: I understand how to use charts to summarize data graphically • LO3c: I am able to produce the vertical barchart for the required data • Please tick the smiley that best describes your feeling for each of the learning objective: