ITPEC IP October 2018 Question 18

Topic: Software

An examination is conducted, and the results of the examination are grouped into four grades “Excellent,” “Good,” “Passed,” and “Not passed,” based on the scores by using spreadsheet software. To show the results of grouping in column C, an expression is entered in cell C1 and then copied to cells C2 through C35. Which of the following is the appropriate expression to be entered into cell C1? Here, the procedure for grouping is as shown below.

[Spreadsheet creation procedure](1)Cells A1 through A35 contain scores.(2)Cell A36 contains a function to calculate the average score.(3)Cell B1 contains the expression below, and this expression is copied to cells B2 through B35.   A1 − $A$36(4)Grades are defined as follows: if the score is above the average by 10 points or more, it is marked as “Excellent”; if it is equal to or above the average by less than 10 points, it is marked as “Good”; if it is below the average by 10 points or less, it is marked as “Passed”; all other scores are marked as “Not passed.”

Explanation

IF (B1 ≥ 10, ‘Excellent’, IF (B1 ≥ 0, ‘Good’, IF (B1 < −10, ‘Not passed’, ‘Passed’))) — B1 is the score minus the average, so each grade is a range of B1.

The grades in (4) as ranges of B1:

B1Grade
10 or moreExcellent
0 up to, not including, 10Good
−10 up to, not including, 0Passed
below −10Not passed

The expression tests B1 ≥ 10 first, then B1 ≥ 0, then B1 < −10; what is left, −10 to just below 0, is Passed. At the boundaries: exactly 10 above the average is Excellent, exactly the average is Good, and exactly 10 below is Passed.

Answer (d)

Why not others:

  • •(a) — the labels are scrambled: a score more than 10 above the average gets ‘Passed’, one just above it ‘Not passed’, and one far below ‘Excellent’
  • •(b) — B1 > 10 puts a score exactly 10 above the average in Good, and the last two labels are swapped: scores more than 10 below get ‘Passed’, while scores at or just below the average get ‘Not passed’
  • •(c) — B1 > 0 for Good lets a score exactly equal to the average fall through to ‘Passed’; “equal to or above the average” needs B1 ≥ 0

Key rule: In a nested IF each test sees only the values the earlier tests rejected; check every boundary value (here 10, 0 and −10) against the wording “or more”, “or less” and “less than”.

Software · AI-written, may contain errors

Practice this exam

This question comes from an official ITPEC past paper. ITPEC Practice is an independent study tool and is not affiliated with ITPEC. See the official IP past-paper collection or Report an issue.