Creating a Multiple Choice Quiz in Excel



Creating a Multiple Choice Quiz in Excel

Open a new Excel document and enter your first question as shown below…

[pic]

In cell B4 we are going to add a combo box that will allow the person taking the quiz to choose from a selection of possible answers, much like a multiple choice quiz.

|[pic] | |

| |Highlight cell B4 then click onto Data – Validation. |

| | |

| |[pic] |

| | |

| |In the allow box select List. Then in the source box this is where you|

| |type in your possible answers. You must separate each choice with a |

| |comma as shown above. You can have as many different possible answers |

| |as you like. |

[pic]

Now if you try clicking on cell B4 you see that a combo box appears which allows you to choose your answer.

Adding an IF Statement

Next we need to add an IF statement telling the player of the quiz if they got the answer correct or not.

[pic]

In cell C4 you need to use the above IF statement. Be very careful of your spelling and make sure you get all the speech marks and commas in the right place.

=IF(B4="Adrian Bothroyd","Correct","Try again")

Test this and see if it works. Save your spreadsheet!

Now try adding some more questions as below…

[pic]

Add in 10 questions. Sensible questions only.

If your going to use answers which are numbers you will need to alter your IF statement slightly.

[pic]

Here you can only select from a number so the IF statement does use speech marks…

[pic]

=IF(B8=10,"Correct","Try again")

Keeping a Score

By using a Countif function like you used in the dice simulator you can keep a running score of how many questions the person taking the quiz has got correct.

[pic]

See if you can add in a score like the example above. Test it and see if it works.

Using Conditional Formatting

Conditional formatting is another feature you would have used in your Year 7 spreadsheet work.

[pic]

Highlight the range of cells with your IF statements and click onto Format – Conditional Formatting on the Excel menu.

You then need to setup 2 rules as shown above. Set a different format for the Correct and Try again cell contents.

Test this to see if it works.

Making your Quiz a Finished Product

If you click onto the combo box and press delete to clear the contents of the cell you will see that the answer cell says you have the wrong answer.

[pic]

To stop this happening you need to alter the IF statement. It needs to read as follows.

=IF(B4="","",IF(B4="Adrian Bothroyd","Correct","Try again"))

Type this in very carefully!

This will clear the cell if nothing is selected in cell B4.

Do this for all your IF statements. Test it and make sure you save your spreadsheet!

Protecting the Worksheet

You will notice there is a flaw with this quiz. If the person doing the quiz clicks onto the cell and looks at the IF statement they will be able to work out what the answer is! (see below..)

[pic]

To stop this from happening we need to protect the sheet so they cannot look at the formula or alter the formulas. Follow these instructions carefully as this can be a bit tricky…

[pic]

Highlight the range of cells with your combo boxes in then click on Tools – Protection – Allow Users to Edit Ranges

|[pic] | |

| | |

| |Then click onto New |

| | |

The window below then comes up displaying the range of cells which you will be able to edit. Click OK.

[pic]

|[pic] | |

| |Then click OK again. |

Now its time to protect the sheet and finish off your quiz.

|[pic] |Click on Tools – Protection – Protect |

| |Sheet |

|[pic] | |

| |You now need to choose a password to protect the sheet. DO NOT FORGET|

| |IT! |

| | |

| |You will need to re-enter the password to confirm you have typed it |

| |in correctly. |

You’ve now done. Save it!

Try clicking on one of your IF statements – you will see you can’t see the formula.

Why not send your quiz to your family and friends? Email it home or put it on your memory stick.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download