In this article we will explain How to Calculate Percentage Score in a Google Forms Quiz
How to Calculate Percentage Score in a Google Forms Quiz : Step by Step Guide
- We have prepared a Sample quiz using Google forms with 3 questions .On providing the correct response the user gets 20 points.Maximum points which a user can score is 60 points.
- Once a user submits a response it will reach our specified destination which is Google Sheets.
- If form which you have created is a quiz Google Sheets will a a new column named Score automatically which will show the total points scored for every response out of the maximum points
Score Convertion to Percentage
As a first step we will convert the recieved score into percentage.You can convert the score to percentage using the array formulas in Google Sheets
How to extract the Obtained Score
You can use the REGEXREPLACE,SPLIT and SEARCH functions to extract the score
Following is the syntax for the same
=REGEXREPLACE(TO_TEXT(B2),"\D.+$","")=INDEX(SPLIT(B2,"/"),1)=LEFT(B2,SEARCH("/",B2)-1)How to Extract the Total Quiz Score
To extract the total Quiz score we can use the below formula
=REGEXREPLACE(TO_TEXT(B2),"\d.+/","")
=INDEX(SPLIT(B2,"/"),2)
=RIGHT(B2,SEARCH("/",B2)-1)
Now right Click on the score column and Select insert 1 column to the right
Now paste the below formula in the cell C1
=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),)))
Once you press enter you will get the result in percentage in the Column C as shown in the above screenshot.
However it is not showing the correct percentage format .To format the result Select the Column C ,then Go to Format->Number and Choose Percent
Now we have the result in Correct format.
The array formula funtion checks the row index and if it turns out to be first row it will add a title name “Percentage”.Then it verifies if a score value exists in Column B if yes then it calculates its percentage.
How to Calculate Percentage Score in a Google Forms Quiz(Conclusion)
In the above tutorial we have provided you steps using which you can calculate percentage Score in a Google Forms Quiz.We hope this tutorial was useful.
Related articles :
How to close a Google Form ( Quick & Easy Guide )
How to Create PDF files from Google Form Response ( Easy Guide)