Multiple choice with radio buttons – Part 3

We will now award points to these numbers, 1 point for a correct answer and 0 points for a wrong answer. We use the ‘CHOOSE” function: Choose function has 2 parameters Parameter 1: index_num (required), indicates the value of the argument that is selected. ‘index_num” must be a number between 1 and 254, or a formula or reference to a cell containing a number between 1 and 254.
Parameter 2: value1, value2, etc – value 1 is required, the remaining values ??are optional. There can be up to 254 value arguments from which the CHOOSE function determines the value or perform an action based on index_num. Arguments can be numbers, cell references, defined names, formulas, functions, or text.

We select cell G2 and type the formula: = CHOOSE (F2, 0, 1, 0)
Parameter 1 (index number) is the cell reference to cell F2.
parameter 2 (value arguments) are the values ??for the answers, radio button 1 wrong answer = 0 points, radio button 2 is correct answer = 1 point and radio button 3 is wrong answer = 0 points.
The formula gives a wrong value if no button is activated, therefore we apply the IF function: = IF (F2 = “”, “”, CHOOSE (F2, 0, 1, 0))
The formula in cell G3: = IF (F3 = “”, “”, CHOOSE (F3, 1, 0, 0) and the formula for cell G4: = IF (F4 = “”, “”, CHOOSE (F4, 0, 0, 1)

We give the result of this quiz again in cell B23.
The formula for cell B23 is:
= IF (SUM (G2: G4) = 1; “You have answered 1 question correctly”; “You have answered” & SUM (G2: G4) & “questions correctly”)

We select columns F and G, right click and choose “Hide” (1).

Then we reset the activated buttons, with the Ctrl key pressed, click on the activated button and click on “Format Control” (1) from the menu.

On the “Control” tab of the “Format Control” dialog box, choose “Unchecked” (2) for the “Value” and click OK.

Repeat this operation for the other activated buttons.

To make this better visually, go to “View” (1) in the  ribbon tab and uncheck the “Gridlines” and “headings” and “Formula Bar” options (2).

The questionnaire is ready to be answered.

Example 2:

multiple choice questions with radio buttons arranged horizontally arranged (1), with the possible answers in cells (2).

Select Sheet 2 of the file.
Go to the “Developers” tab on the Ribbon, click on “Insert” and select “Radio Button”.
The cursor changes into a cross, go to cell C4 and draw a rectangle,while holding down the left mouse button, approximately the size of the cell and release the left mouse button. With the button still selected, right click on the button and click “Edit Text “, press the Delete key and delete the text. With the box still selected, go to one of the corners of the box and resize it so that you have a square, you can now use the arrow keys on the keyboard, center the selected radio button in the cell (1).

Move the mouse cursor to one of the sides of the button, the cursor now changes into a four arrow cross, press the Ctrl + Shift key, a plus sign will appear next to the cursor. Copy the radio button to the middle of cell D4 while holding down the left mouse button, release the left mouse button, press it again and copy the button to cell E5, release the Ctrl + Shift + the left mouse button and click anywhere on the worksheet (1).

Press the Ctrl key and click successively on the circles so that all three are selected, go to the  “Developers ” tab in the “Ribbon”, click on “Insert” and select “Group Box”. The mouse pointer now changes into a cross, draw a group box such that the radio buttons are completely enclosed by the group box, make sure that there is space between the bounding boxes of the radio buttons and the group box and click anywhere on the worksheet.

Right click on the text “Group Box 1” and click “Edit Text” and delete the text with the Delete key. With  the group box still selected, click with the right mouse button on one of the selected lines of the box and choose “Format Control”. In the dialog box, click 3-D shading and then click OK.
With the group box still selected, move the cursor to the left line of the selection box and drag it to the left with the left mouse button pressed, so that it is also included in the group box (2).

Click anywhere on the worksheet, press the Ctrl key and click successively on the three selection buttons and the group box so they are all selected, copy them down using the method described in Example 1 for the 2 remaining questions (1).

Linking the buttons to a cell, 3D shading of the buttons and the formula for the cells can be done in the same way as we didin the Example 1.

What can go wrong in the creation of radio buttons

1. They should not overlap, otherwise they react with each other.
2. They must be completely enclosed by the group box, otherwise they can respond to buttons that belong to another group box.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats