Get Shift Done: Tips and Tricks
Quizzes can be a real benefit for marketers — and for plenty of other professionals. They can be frivolous or serious, ranging from fun quizzes to increase traffic, to market research surveys, to weeding out the hacks from the experienced potential hires.
To demonstrate how the quiz feature can work, we use an example of a basic knowledge test to be used when hiring a search engine optimization specialist for our marketing team. We expect a lot of applicants, so it makes sense for us to look at resumes only from the people who know the right answers, which means sorting them by some kind of “grade.”
From Google Drive, click New, then More, and choose Google Forms — or head directly to Google Forms.
First thing: Click on Responses to tell Google Forms what to do with the data it collects.
Then choose Select response destination.
For creating and grading quiz responses, we want to Create a new spreadsheet. If you’re following along, name it “Quiz (Responses).”
Add the quiz questions, starting with the applicant-name field and then your SEO questions.
Make sure the SEO questions are all multiple-choice. Your answers need to be exact for the calculations to work, so multiple choice is the best way to go. In this case, I used a “fill in the blank” question and answer format. You can use whichever format you like, as long as you have the multiple choice answer.
When finished, close out your form.
Next, open the response sheet that Google created in your Drive.
As it stands, the SEO quiz would collect answers and save them here. That’s fine for plenty of form-creation needs, but the goal is to provide a grade automatically. So we have a few more steps.
In the response sheet, add two more rows above your questions. In the first column, above Timestamp, add the Answers on the first row, and Value in the second row. In the Answers row, type in the correct answer for each question, and in the Value row provide a numerical value. Your value can be anything you want as long as it is a number value above 1.
In this SEO quiz example, each right answer is assigned 5 points, with a possible total of 20 points for getting 100% correct responses.
Add a new tab to the Google Sheet that collects the raw data. Name the new tab Test Scores. This is where you capture the quiz-takers’ names and the calculation of their scores.
Give your columns the three headings: Name, Points, and Percentage. In the Name column, we pull in the name of the quiz-taker for each record by pulling in the “Form Responses” first record name. Then we pull down the formula for the number of people who take the test (which is invite-only for this example project, with 15 applicants).
We need the formula that calculates whether the test-taker’s answer is correct and assigns a value to the answer. To do so, we use the IF statement to judge whether the respondent’s answer matches our correct answer, assigning the score if the answer is correct and 0 if it is wrong.
Add each answer’s score. Simply repeat the IF statement for each question and answer, adding them all on the same formula line with a + between each IF clause. It looks something like this, when you’re done:
=IF(‘Form Responses 1’!B4=’Form Responses 1'!$B$1,’Form Responses 1'!$B$2,0)+IF(‘Form Responses 1’!C4=’Form Responses 1'!$C$1,’Form Responses 1'!$C$2,0)+IF(‘Form Responses 1’!D4=’Form Responses 1'!$D$1,’Form Responses 1'!$D$2,0)+IF(‘Form Responses 1’!E4=’Form Responses 1'!$E$1,’Form Responses 1'!$E$2,0)
The result of that formula is the total points the applicant scored divided by the total possible, creating the percentage value of his test results.
We have to turn that result into a score to show to the hiring manager. To do so, we calculate the percentage of the total correct answers with the total possible by dividing the applicant’s total points with the total possible in the “Form Responses” tab. For our example, that’s the ideal score of 20.
Set your column to format to a percentage.
Now when your user takes the test using the form, his name is entered into the “Quiz (Responses)” spreadsheet, and the answers automatically added. The Form Responses 1 tab (the one that Google created) shows the raw data.
Now, however, look at the second tab, where you added all those formulas. You’ll see the quiz-takers’ scores already calculated for you!
This simple-to-use feature makes it easier to evaluate the results of quizzes, surveys, and other data gathered through Google Forms. And, of course, it’s only the beginning. With more complex analysis, reporting, and charts, you can turn Google Forms into a mighty tool indeed.
GSD: Tips and Tricks is brought to you by Xero, the cloud accounting software solution for your small business. With Xero, you can log in anytime, anywhere to get a real-time view of your cash flow and manage your books. Start your free 30-day trial today.