IF A1 >= 70 THEN OUTPUT "Pass" ELSE OUTPUT "Fail".
To understand the IF function you have to understand the IF..THEN and IF..THEN..ELSE constructions. The IF part examines the condition, the outcome of which must be TRUE or FALSE. If the condition is TRUE the FIRST action is taken; action if the condition is FALSE the SECOND action is taken. The condition is defined with the operators =, <, >, <=. >= and <>. These are known as Boolean operators after the mathematician George Boole who devised them.
For example:
A1=70 is either TRUE or FALSE.
A1>B1 is either TRUE or FALSE
A1<> George is either TRUE or FALSE, and so on.
IF..THEN..ELSE statements can be extended or nested almost as far as you wish into, for example:
IF A1>70 THEN OUTPUT A
ELSE IF A1>60 THEN OUTPUT B
ELSE IF A1 >50 THEN OUTPUT C
ELSE OUTPUT D
In this case there are three conditions to be tested with four possible outcomes. If a pupil scores 70 or more she gets an A; if she gets more than 60 she gets a B; if she gets more than 50 she gets a C, otherwise she gets a D. Different programming languages have slightly different ways of implementing this sort of structure. In Excel it would be:
=IF (A1>=70,A, IF (A1 >=60, B, IF (A1>=50, C, IF(A1>=40, D))))
If a pupil gets less than 40 marks the cell will read FALSE which is not ideal in this case. The line can be amended to:
=IF(C3<40,"F",IF(C3>=70,"A",IF(C3>=60,"B",IF(C3>=50,"C"))))
With this small amendment pupils who gain less than 40 marks will be awarded a grade "F". Here is the spreadsheet in construction:
The formulae are copied into rows 4-7.
To add the rank of each candidate enter =RANK(range) in a suitable cell such as E3 (use the mouse to set the range).
Use the Back Button to Return to the previous page