Introduction

For this lesson the students will create their own maths based quiz, where the answers will be automatically marked correct or incorrect.  It would be useful, but not essential, if the students could write their questions in advance of the lesson. 

Calculations using all four operations and definitions of mathematical concepts are good starting points.  There are some examples already in the spreadsheet ‘Demonstration spreadsheet’, worksheet ‘Questions’.

 

 

Making a self marking quiz

Open the spreadsheet ‘Demonstration spreadsheet’, worksheet ‘Questions’.

Look at the definitions and ask the children to suggest answers.

(1 = square, 2 = addition, 3 = triangular prism).

 

Type the children’s suggested answers into column C.

Ask the children: why did the cell colour change when answers are added?

If the answers are correct then the cell will turn green.

If the answers are incorrect or typed incorrectly then the text will turn red.

 

Which questions did the class answer correctly?  Which were incorrect?

Talk about the answer to question 3

'Name the prism with the cross section of a triangle'.

 

Could there be more than one correct answer to this question?

E.g. triangular prism or triangular based prism.

 

Change the answer to question 1 to ‘squre’, what happens?  Can you explain to your partner why? 

Ask the class to feedback once they have discussed this.

The answer must be exactly square, computers don’t mark ‘nearly right’ or ‘close’.

 

Click in cell C3 and open the conditional formatting.

 

 

Cell C3 has two rules to mark the answer as correct or incorrect and unlike a teacher there is no formatting that will correct the answer or explain why something is nearly right.

 

 

Ask the class what would the conditional formatting for cell C5 be?.

Open the conditional formatting window to check the responses.

 

 

Can the class to think back to the last lesson, why might using conditional formatting might be useful?

Setting a cell to change colour helps to highlight specific information, especially in very large data sets.

This can be used to identify all the cells containing the same information or to highlight inaccuracies or errors in information.

 

Ask the class to suggest their own question e.g. Which triangle has no equal sides or angles? 

Scalene

 

Write the question in column B.

In column C select conditional formatting and write the rule to mark ‘scalene’ correct.  Check the formatting by entering the correct answer in column C.

In column C select conditional formatting and write the rule to mark ‘not scalene’ incorrect.  Check the formatting by entering an incorrect answer in column C.

 

Repeat if needed

e.g. 

• 33 x 10?
  • 330
• What do you call the bottom number in a fraction that shows how many equal parts the item is divided into?
  • Denominator

Here it is important for the pupils to understand that the question that is asked must have only one possible correct answer.  Remind them about question three.  The answer triangular based prism is more correct but triangular prism could also be acceptable.

 

Main tasks

Explain the final spreadsheet task to class.

Each child will create their own self marking maths quiz.

Ask the children to open the Activity sheet and to complete Task 1 and Task 2.

 

As a class, discuss Task 3.  When will a correct answer be marked incorrectly?

For the conditional formatting to work the pupils need to know the correct answer AND correctly set the conditional formatting.

Spend some demonstrating adding a very simple question to the ‘Quiz’ spreadsheet e.g 4+3=

Using conditional formatting, mark the answer as ‘text is exactly 8’ as the correct answer. How do you feel when the correct answer is repeatedly marked as incorrect?

When the majority of pupils have three or more questions written, they can then share their quiz sheets with others in the class.

Demonstrate how to share a document.  Make sure that each pupil in the class receives at least one quiz to try and evaluate.

 

In Google Sheets, click on the ‘Share’ on the top right hand side of your screen.

 

 

Type the email address of someone in your class into the ‘Share with people and groups’ field.

Type a message and click ‘send’.

 

 

Students should receive an email with a link to a quiz from someone in the class.

 

Other ways to share

Sharing can also be done in Microsoft Office 365 in the same way, or alternatively, Excel spreadsheets could be saved to a folder on your school's shared area so the students can access each other's quizzes. Make sure they DO NOT save any changes when they close the documents having completed a quiz.

 

Allow the students some time to test out each other's quizzes before they improve their own sheets.

 

Plenary

Ask the students to feedback to each other about the quizzes they completed. This could be done verbally, via email or by leaving comments on the spreadsheet.

 

To add a comment, select the cell you wish to comment on.

 

Go to the Insert menu and choose comment.

 

 

This can also be done from the main tool bar by clicking the + comment icon.

 

 

Type your comment in the box that appears. Then click comment.

 

 

Cells with comments attached can be identified by a yellow marker in the top right corner. As you hover your mouse cursor over them the comment will appear. The owner and editors of the spreadsheet will also get email notifications of any comments added to their sheet.

 

 

Students can then respond to any comments where necessary. If it is suggesting a change they can make the fix and click the green tick on the comment to mark it as resolved, which also removes the comment. Alternatively, they can respond with text in the reply box, if for example, it's a message of praise. If you click the three dots in the corner of a comment you can also edit or delete it.

 

 

Finish off by asking the class what they know about spreadsheets that they didn't know at the start of the topic. Then ask them to pair up with a partner and take it in turns to explain the following terms to each other:

 

Partner A

• Cell
• Row
• Sorting
• Charts

 

Partner B

• Column
• Cell reference
• Formula
• Conditional formatting

 

Choose some of the terms to go over at the end, asking students to share their explanations with the class and add to, or improve them where they can.