11a)

Construct a nested formula in cell B4 using the Match function and other functions, that retrieves the score of an assessment based on the Student ID and Assessment Code entered in cells B2 and B3 respectively. If the Student ID or the Assessment Code are not found from the table, your formula will display Invalid input in the cell B4.

11b)

Construct a formula in F9 which provides the final score which is calculated based on the weight factor for each assessment. This formula should be constructed in such a way that it is easily copied to cells F10:F18.

11c)

Using the grading scale in range I14:J18, construct a VLOOKUP function in cell G9 to determine the grade based on the final score in cell F9. This formula can be copied to cells G10:G18 without modifications.

11d)

Using nested IF functions, you may achieve the same result for question (c). Construct a formula using nested IF functions in cell G9 to determine the grade based on the final score in cell F9. This formula can be copied to cells G10:G18 without modifications.

11e)

Construct a formula in B20 which gives the average score for the unit.

11f)

Construct a formula in B21 which shows the number of students that scored less than the average final score.

11g)

Briefly describe the steps to define a name for a range. For example, you want to name the range I14:J18 as Grading scale.

11h)

Briefly describe the steps where you protect this worksheet to prevent users from changing cell contents, and only allow users to edit the range B2 to B3 after the worksheet is protected?

A B D E F G H I J 1 2 Student ID 1