Calculating Percentage Score in Google Forms Quiz: Step-by-Step Guide

Calculating Percentage Score in Google Forms Quiz: Step-by-Step Guide

With our detailed instructions, you can figure out how to calculate percentage results in Google Forms quizzes. To get precise percentages, automate scoring, extract scores, and apply straightforward formulas. Learn how to use Document Studio to automate the procedure and issue certificates as rewards to the participants. This beginner’s guide is ideal for instructors, trainers, and quiz aficionados and will help you improve your quiz experience and easily evaluate participants’ performance. Start right away!

We have the perfect solution for you if you want to use Google Forms to build an entertaining and engaging quiz. Our short quiz has three questions, and the respondent receives 10 points for each accurate response, for a possible total of 30 points.

All of the results are neatly recorded in a designated Google Sheet after someone completes the quiz and submits the form, which serves as the Google Form’s response destination.

What’s best? Google Sheets will automatically add a new column called “Score” to the response sheet if your form is a quiz. The total score each responder earned on the quiz will be automatically filled in this column.

Now, let’s take a look at how you can convert the quiz score into a percentage:

Extract the Score Obtained:

Any non-digit characters will be replaced with a blank by the REGEXREPLACE function, leaving only the score.

=REGEXREPLACE(TO_TEXT(B2),"\D.+$","")

The score column’s text is divided using the SPLIT function with the slash (/) serving as the delimiter. The score is then represented by the divided array’s first value, which is retrieved by the INDEX function.

=INDEX(SPLIT(B2,"/"),1)

As an alternative, we can locate the slash in cell B2 using the SEARCH function and then use the LEFT function to retrieve everything that comes before the slash.

=LEFT(B2,SEARCH("/",B2)-1)
Extract the Total Quiz Score:

By checking for the number following the slash in the Score column, we can use a similar method to find the quiz’s highest possible score.

=REGEXREPLACE(TO_TEXT(B2),"\d.+/","")
=INDEX(SPLIT(B2,"/"),2)
=RIGHT(B2,SEARCH("/",B2)-1)
Calculate the Quiz Percentage:

We can now combine the methods we previously used to independently extract the quiz score and the overall score to obtain the percentage score.

=REGEXREPLACE(TO_TEXT(B2),"\D.+$","")/REGEXREPLACE(TO_TEXT(B2),"\d.+/","")
=INDEX(SPLIT(B2,"/"),1)/INDEX(SPLIT(B2,"/"),2)
=LEFT(B2,SEARCH("/",B2)-1)/RIGHT(B2,SEARCH("/",B2)-1)

Follow these procedures to automatically include these formulas for each new quiz form submission:

  • Select “Insert 1 column left” from the contextual menu by performing a right-click on the score column.
  • Insert any of the formulas listed above in cell C2.
  • Copy the formula down to more quiz response rows.
Automate Formula Application:

The “copy formula down” method can be used to automatically add the formulas each time a new quiz form is submitted, saving time and effort. This is how:

Go to cell C1 and paste the following formula:

=ArrayFormula(IF(ROW(B:B)=1, "Percentage", 
IF(NOT(ISBLANK(B:B)),LEFT(B:B,SEARCH("/",B:B)-1)/RIGHT(B:B,SEARCH("/",B:B)-1),)))

If it is the first row, the algorithm determines whether to append the column header. If there is a score value in column B, the percentage score is then calculated.

To appropriately format the derived percentage, select column C and choose Format > Number > Percent.

You’ve successfully constructed a Google Forms quiz with automatically computed % scores by following these easy instructions! You may now deliver certificates based on quiz results using Document Studio as well.

Enjoy developing interesting tests and utilizing Google Forms’ robust capabilities!

Leave a Comment