  # 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. 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. ## 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.

``````=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. 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. 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”