Introduction to Spreadsheet

ICT122 – Computing Skills Fundamentals II (Education) Lecture Note Set 2 G. Anderson 2013 Topics: IF function, Nested IF, SUMIF, COUNTIF, COUNT, other functions.

Figure 1: Marks for ABC123

1. Given the worksheet above (Fig. 1), write a formula to calculate the FINAL mark for the first students, such that it can be copied for the other students: ____________________________________ 2. Given the worksheet above, write a formula to assign a letter grade to the first student, such that it can be copied for the other students. The cut-off marks should be utilized as values in your formulas:

___________________________________________. Recall that when using two categories (Pass and Fail), one could enter: =IF(D8>=50,”Pass”,”Fail”), read as: “if the final mark is greater than or equal to 50, then Pass, otherwise Fail.” 3. Same as 2 above, but this time use cell references for the cut-offs, such that one just has to change the “Cutoffs:” table in order to use new cut-offs for the students (hint: consider the use of relative and absolute cell references): ______________________________________________. 4. COUNT, SUMIF, COUNTIF: a. The COUNT function has the following syntax: COUNT(value1, [value2],…). It counts the number of values in its parameter list.

Page 1 of 2

© G. Anderson 2013

b. SUMIF(range,criteria, [sum_range]). Range is the range that should be evaluated by the criteria. Criteria could be “<25” for example (remember logical operators); in which case we want to add up those values less than 25. Sum_range (optional) is the range to sum up if different from the criteria. c. COUNTIF(range,criteria): we count, for example all cars whose color is blue: =COUNTIF(B2:B50,”BLUE”) 5. For the worksheet for figure one write formulas to solve the problems described in A10:A12, using the functions described in 4 and any other necessary functions. For the “Average Mark for A Students” you need to add up the final marks for A students and divide by the number of A...