Learn how to use a Google Sheets formula to find the percentage score of a Google Form quiz.
We created a simple quiz in Google Forms that contains 3 questions and gives 10 points for each correct answer. The maximum number of points that can be achieved in the quiz is 30 points.
When someone takes the quiz and submits the form, the responses are recorded in a Google spreadsheet set as the response destination for your Google form.
There is something interesting here too. If the associated form is a quiz, Google Sheets will automatically add an additional column titled “Score” to the answer sheet, and that column will be populated with the total score the respondent received on the quiz.
Convert quiz score to percentage
A teacher may wish to calculate the percentage points students have achieved on the quiz and assign grades accordingly. This can easily be done using array formulas in Google Sheets, but before we get to that, let’s see how we can convert the quiz score (e.g. 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.
REGEXREPLACE The function replaces each string value that matches RegEx with another value. Here we start with the first non-digit character in the cell, match everything to the end of the string, and replace it with a space. Thus, the slash (/) and everything after the slash is replaced and we are left with the score.
For the second approach we use the
SPLIT function to split the text in the score column using the slash as the delimiter, and then use the
INDEX Function to get the first value of the split array containing the score.
In the next approach we use the
SEARCH function to determine the position of the slash in the cell and use the
LEFT Function to get everything before the slash.
We can use a similar approach to get the maximum score of a quiz, and that number comes after the slash in the Score column.
=REGEXREPLACE(TO_TEXT(B2),"\d.+/","") =INDEX(SPLIT(B2,"/"),2) =RIGHT(B2,SEARCH("/",B2)-1)
Calculate the quiz percentage
Now that we have formulas to extract the quiz score and the total score separately, we can combine them to get the percentage score.
Your options are:
=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 on the points column and select
Insert 1 column left from the context menu and paste one of the above formulas into cell C2. You can then copy the formula to other rows that contain the quiz answers.
Automatically copy quiz score percentage down
A disadvantage of the previous approach is that you have to add the formulas in the row every time a new quiz is submitted.
A simple workaround to the problem is the copy formula down approach, which automatically adds the formulas when a new quiz form is submitted.
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, adds the column title. Next, it checks if there is a score value in column B and then calculates the percentage score.
Next, select the C column, go to
Format > Number > Percent to properly format the calculated percentage.
You can also use Document Studio to submit certificates based on quiz results.