Microsoft Excel - Intermediate Level

Creating task sheets for pupils - exploring answer checking, cell protection and hiding formulae.

Excel is a useful tool for allowing pupils to enter answers to questions and get an immediate response, e.g. whether or not the answer they have entered is correct. It is possible to check for both numbers and/or text having been entered. The functions to allow this to work have already been covered, to a certain extent, by the Baglan Books exercise. These will include IF functions and Conditional Formatting. Download the Maths Quiz spreadsheet here.

Getting started

Run Excel and in the first worksheet, make a suitable question sheet which will require an input from pupils (make up some simple maths examples whilst practising this task). Make sure that it will print out correctly on an A4 sheet of paper. In the example opposite, I have placed borders around the cells where input is required, and labels next to those cells which may require clarification as to whether or not the label is required as part of the answer (e.g. kgs for kilograms and k for kilometers). When the layout is complete, save the file.

To check the answers, you will need to use the IF function. However, IF can only check for a cell's contents to be in either of two states; the answer cell can be in three states - blank (question has not been answered yet), it contains the correct answer or it contains an incorrect answer. To get around this, you need to use nested IF functions, i.e. an IF function inside an IF function!

Start by selecting (clicking on) the cell which is to contain IF function (H4 in the worksheet image above), press the = key (or Select the = sign) and Select the IF function from the function list. For the first parameter, enter the cell to be checked (F4 in this case) and make it equal to "" - two double quotation marks (blank, in other words). The Value_if_true parameter will also be "", in other words, if cell F4 is blank, then display nothing. The Value_if_false parameter will be the second IF function, so Select the IF function again (it should still be displayed on the function selector). Enter the parameters as if you were simply checking for the correct answer, then Select OK to finish. Your completed function should look like this (based on the layout above):

=IF(F4="","",IF(F4=3.44,"Correct","Wrong"))

Check that this works by entering a wrong answer and then a correct answer. Finally delete the answer to confirm that the check cell returns to its blank state. If all works correctly, enter similar nested IF functions in the other check cells and save the file.

For those of you who are daring enough, you may want to include other checks for when the answer is close to the correct one, e.g. displaying the word Careful or even providing some help. You can nest up to seven IF functions!