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.

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

OptionButton12

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.

optionboxessurvey02

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.

optionboxessurvey01

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

optionboxessurvey03

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.

optionboxessurvey04

Watch the 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.

_____________

You may also like...

5 Responses

  1. Rick Lewis says:

    Hi, this is a lttle over my head. Here is my problem and what I’m looking for. Our middleschool puts out a monthly newspaper that includes a monthly survey for the students (5th 6th grades) to fill out. Very simple questions with multiple choice answers (A,B,C,D circle one). As they are turned in I enter them into an Excel spreadsheet, sort the answers and figure the percentages to inter into the results in the next newspaper.
    I was hoping there was a survey tool that was simple to use that could cut some of the work I am currently doing.
    Could I get a response by email?
    Thank you, Rick

  2. Marcelo says:

    Hi, this is a lttle over my head. Here is my problem and what I’m looking for. Our middleschool puts out a monthly newspaper that includes a monthly survey for the students (5th 6th grades) to fill out. Very simple questions with multiple choice answers (A,B,C,D circle one). As they are turned in I enter them into an Excel spreadsheet, sort the answers and figure the percentages to inter into the results in the next newspaper.
    I was hoping there was a survey tool that was simple to use that could cut some of the work I am currently doing.
    Could I get a response by email?
    Thank you, Rick

    http://metodoexcel.com.br/pincel-de-formatacao/

  3. Gavin says:

    Hi,
    Similar, but naturally different to the above. I’m a teacher and we reward children with House Points (I know, sounds like Harry Potter). Keeping track of who has been awarded how many points is becoming a bit of a headache. I’d like to be able to keep a spreadsheet open and if I award a House Point, I can just click a ‘+1’ button next to their name to increment their number of house points by one. Then, periodically they will ‘cash in’ these points and their score will be reset to zero. Ideally this last part would be done by a reset button.

    Can anyone offer some advice for how to do this?

    Thanks,

    Gavin

  4. Abeer says:

    Hello, I would like to have score costing sheet for collecting multiple survey result

Leave a Reply

Your email address will not be published. Required fields are marked *