Automatically assigning Symbols by using nested IF statements

If you want to automatically work out a Grade for each student based on a mark or percentage, you can use IF statements.

Test A and B have different grade boundaries which will determine whether a student achieves a final grade of "distinction, merit, pass or fail". Your grade could be something like: "A, B, C, or D"; or "Achieves the Outcome, etc" instead.

Click here to open this starting spreadsheet.

To automatically insert the Grade, you will use the IF function. The IF function tests the value in a cell and does one thing if the test is true and another if the test is false. You will test each percentage against the grade boundary. For example, for test A, if the percentage is greater than 80%, then the student gets "distinction". Unlike the basic IF function which tests one condition; we have to test each condition against the three grade boundaries. So for this you will learn how to use a nested IF statement.  

To construct the formulas, you will use a nested IF statement and absolute referencing.  

Note: for simplicity, this example uses only one word (eg “Distinction”); but you could enter a whole phrase (eg: “exceeds the expectations” or “partially achieves the expectations”).  

This is the logic in English.  

If E8 > B19, then the grade is distinction;
if it is not, then,

    if E8 > B20, then the grade is merit;
    If it is not, then,

        if E8 > B21, grade is pass;
        if it is not, then if E8 < B21, grade is fail.

This is the formula in expressed in a way that Excel understands. Colours have been used to indicate how the formula relates to the English version.  

= IF(E8>$B$19,"Distinction", IF(E8>$B$20,"Merit", IF(E8>$B$21,"Pass","Fail")))

Type this formula in cell F8, and Autofill it into cells F9 to F14.  

Work out the formula you need to enter in cell I8 to calculate the grades for Test B. Your spreadsheet should now look something like this: