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.
_____________
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
Rick,
Yes there is. https://support.office.com/en-us/article/Surveys-in-Excel-hosted-online-5fafd054-19f8-474c-97ec-b606fcda0ff9
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/
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
Hello, I would like to have score costing sheet for collecting multiple survey result
I also want to remove the weight of N/A from calculations