free webpage hit counter

How to calculate a percentile score on a Google Forms quiz

Learn how to find the percentage score for a quiz in a Google Form with the help of a Google Sheets formula.


Google Forms Quiz

We have created a simple quiz on Google Forms with 3 questions and each correct answer will give you 10 points. Thus the maximum score that can be obtained in the quiz is 30 marks.

When someone takes a quiz and submits a form, responses are recorded in a Google Sheet set as the response destination for your Google Form.

Here is something interesting. If the combined form is a quiz, Google Sheets will automatically add an additional column to the response sheet titled “Score” and this column will be populated with the total score the respondent received on the quiz.


Google Forms Quiz Score in Google Sheets

Convert quiz score to percentage

Teachers may want to calculate the percentage of marks scored by students on a quiz and assign grades accordingly. This can easily be done with the help of array formulas in Google Sheets but before we go there, let’s see how to convert a quiz score (say 20/30) into a percentage.

There are at least three ways to extract the quiz score obtained from cell B2. Let’s explore some of them.

The REGEXREPLACE The function replaces any string value that matches the RegEx with another value. Here, we start with the first character in the non-numeric cell, match everything up to the end of the string, and replace it with a blank. Thus the slash (/) and everything after the slash is replaced and we are left with just the score.

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

For the second method, we use SPLIT A function to split the text in the score column, with a slash as the delimiter, and then use INDEX A function that gets the first value of a partitioned array containing score.

In the next method, we use SEARCH Use and to determine the position of the slash in the cell LEFT Function to get everything before the slash.

=LEFT(B2,SEARCH("/",B2)-1)

We can use a similar method to get the maximum score of a quiz and that number is after the slash in the score column.

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

Quiz percentage calculation

Now that we have the formulas to extract the quiz score and total score separately, we can combine these to get the percentage score.

Your options:

=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)

Right-click the Score column, select Insert 1 column left From the context menu and paste any of the above formulas into cell C2. You can then copy the formula to the other rows containing the quiz responses.

Copy quiz score percentage automatically

One drawback of the previous method is that you need to add formulas to the row each time a new quiz is submitted.

A simple solution to the problem is the Copy Formula Down method, which automatically adds formulas whenever a new quiz form is submitted.


Quiz Percentage Formula

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),)))

It looks at the row index and if it’s the first row, it adds the column heading. Next, it checks if there is a score value in column B and then calculates the percentage score.

Next, select column C, go Format > Number > Percent To format the calculated percentage correctly.

You can also make Document Studio send certificates based on quiz scores.

Leave a Reply

Your email address will not be published.

Previous post XAUUSD shed $20 on daily high post-US jobs data
Next post Artist found dead in Swansea park “has faced years of anti-gay bullying”