Calculate Survey Scores with Excel Option Buttons

For easier data entry, you can add Option Buttons on a worksheet. Instead of having to type an answer to a question, just click on one of the buttons, to make a choice. Here’s how to calculate survey scores with Excel Option Buttons.

Add the Option Buttons

When you set up the buttons, you can create a simple option group, with only two buttons, like the English or French example shown below.

There are set up instructions in this blog post, and in the video shown at the end of this article.

Create a Survey with Option Buttons

If two options aren’t enough, you can set up something fancier, like Dave Peterson’s Excel Survey Template, that he shares on my Contextures website.

It uses programming to create Group Boxes, with six Option buttons in each group. The buttons in each group are linked to the Response column in that row.

In the screen shot below, the selected option button, and all the others in that group, are linked to cell C2, as you can see in the formula bar.

In row 2, the first button is selected, so the Response cell shows 1. In row 5, the 6th button is clicked, and 6 shows in the Response cell.

Calculate the Score

In Dave’s survey file, there is a Weight column (B), and the Score is calculated in column A, using this formula (cell A2):

=IF(C2=””,””,IF(C2=6,”N/A”,B2*(C2-1)))

• If no response has been selected, the Score shows an empty string
• If the response is 6, the Score shows as “N/A”
• For any other response, 1 is subtracted from the number in the Response cell, and that amount is multiplied by the number in the Weight column.

Create a Score Lookup Table

In some surveys, you might want a different type of scoring calculation. Perhaps responses 1, 3 and 5 are worth 1 point, 2 and 4 are worth 2 points, and response 6 means “N/A”.

You can set up a table that shows those scores, as in the screen shot below. The lists are named ranges:

• RespList – cells M2:M7
• ScoreList – cells N2:N7

Change the Formula

Next, you can change the formula in column A, so it finds the score for the selected response. The formula refers to the named ranges in the Score Lookup table.

=IF(C2=””,””,IF(C2=6,”N/A”,B2 * INDEX(ScoreList, MATCH(C2, RespList,0))))

• If no response has been selected, the Score shows an empty string
• If the response is 6, the Score shows as “N/A”
• For any other response, INDEX and MATCH return the score for the selected response, and that amount is multiplied by the number in the Weight column.

Option Button Setup Video

To see the steps for manually creating a Group Box with Option Boxes, watch this short video.

Download the Sample File

To see Dave’s setup code, and the score calculation formulas, you can download the sample files from my Contextures website. The zipped file is in xls format, and contains macros.

_____________