Looking for an easy way to calculate percentage score in Google Forms?
If you use Google Forms, you’re likely familiar with the challenge of not being able to calculate fields directly within the platform. Whether you need to determine the percentage score of a quiz or tally the total sales items, this limitation can be frustrating.
Despite this, don’t abandon Google Forms just yet! While it doesn’t offer built-in calculated fields, effective workarounds are available. In this article, we’ll explore these methods, making it easier for you to calculate percentage scores in Google Forms, whether for quizzes or other types of forms.
Table of Contents – Percentage Score in Google Forms
Method 1: Calculate the Percentage Score in Google Sheets
Whether you want to calculate scores of a quiz or percentage of the total avenues of the sales. You can easily do that in Google Sheets with the help of formulas. The solution requires some technical knowledge, depending on how much calculation you want to do. However, with spreadsheet formulas, you can quickly calculate percentage scores.
To calculate the percentage score in Google Forms with the help of Google Sheets,
First, we need to create or link a spreadsheet to your form;
- Click on the ‘Responses’ tab in Google Forms, then click on “Link to Sheets”
- This will create a new spreadsheet with all the responses automatically added to it as a response destination to Google Forms.
- An additional point of interest is that if the Google Form is a quiz, a new column called “Score” will automatically appear in the response sheet of the associated Google Sheets document.
- All the point values obtained by the respondent will be added here with the total. For example, – 20/30 like this the score column will be populated.
We can through Array formulas easily calculate the percentage score obtained by respondents. However, if you want to calculate the percentage score in Google Forms for a quiz then there is another way through which you can calculate percentage scores, total scores, etc.
Enhance security by adding CAPTCHA to Google Forms. Read, How to Add CAPTCHA to Google Forms? (4 Easy Steps) to know more.
Unlike the Array formula, in this copy formula down approach, you can’t automatically copy down the formula to the entire spreadsheet column. Instead, you’ll have to select a cell in which you’ve added the formula and use the fill handle (black plus symbol) that you can drag down to all the cells you want to fill.
[Note* If you want to copy down the formula across the cells sans formatting, then Ctrl + C (copy) the formula and select the range where it has to be applied, right-click choose Paste Special, and Formula only.]
Its only drawbacks are, that it can be time-consuming if there are hundreds of rows and besides, every time new responses are added, you’ll have to add the formula each time in the added new row.
However, it can be used if you have less range of rows of a column that you want to calculate the percentage score of. We will discuss both methods here.
But before we get into Array Formula, let’s go through the process of converting the quiz score (say, 25/40) into a percentage. Besides, we can also discuss a bit how to sum up a range of numbers for the total and then convert it into a percentage.
Note* It is not necessary to go through all the formulas I have mentioned here till now whether it’s to extract the score or total score. You can skip all of it (if not required) and directly follow from the ‘calculate quiz percentage score” section.
Extract the Obtained Score
There are at least three ways to extract numbers (scores) from a string that contains variables, expressions, & non-digit text. Let’s explore some of them;
(So to understand these formulas, let’s implement the formulas in the B2 cell (means, in the formulas, we will add B2 and if you want to individually copy the formula to another cell keep in mind to change it to a specific row number of the certain column).
The REGEXREPLACE function can be used to remove any non-digit characters from your string (in here, scores). For example, a score of 80/20 will return a value of 80. This formula is specific to extracting the score obtained.
=REGEXREPLACE(TO_TEXT(B2),"\D.+$","")
There is another formula that you may use (but not in the case of extracting scores), if you have a string like HAPPY11 or something that contains characters and digits, then the REGEXREPLACE function will return a value of the number (in the case here, 11).
=VALUE(REGEXREPLACE(A2,"\D+", "")
The REGEXREPLACE function requires three inputs: the original text, a regular expression, and a replacement text. In the formula mentioned above, certain characters are replaced with an empty string or “ ” using the regular expression “\D+”, effectively removing them from the result.
The “\D+” expression matches one or more non-digit characters. To convert the resulting string into a numerical value, we apply the VALUE function to the output.
In the alternative method (specific to extracting scores), we utilize the SPLIT function to divide the text within the score column using a slash as the separator. We then utilize the INDEX function to obtain the initial value within the split array, which corresponds to the score.
=INDEX(SPLIT(B2,"/"),1)
Similar to the REGEXREPLACE function, the SPLIT function has simultaneously a different method that can be used if you want to extract numbers and split them. This function only has the SPLIT function and not the INDEX function to obtain the initial value without a split array.
=SPLIT(lower(D3),"qwertyuiopasdfghjklzxcvbnm`-=[]\;' ,./!:@#$%^&*()")
Extract the Total Quiz Score
Similar to how you extract the score, you can extract with the below formulas the total score of the quiz.
=REGEXREPLACE(TO_TEXT(B2),"\D.+$","")/REGEXREPLACE(TO_TEXT(B2),"\d.+/","")
=INDEX(SPLIT(B2,"/"),1)/INDEX(SPLIT(B2,"/"),2)
Calculate a Quiz Percentage Score
Now that you’ve extracted the scores and total scores, you can calculate the quiz percentage score through the below formula. To calculate the percentage score add a column after the SCORE or before the start of the answers – C2 cell.
Then add the formula and copy it down to all the cells. To format the result into the percentage click go to Format > Number > Percent. And if you want the whole number of the percentage move the decimals.
=INDEX(SPLIT(B2,"/"),1)/INDEX(SPLIT(B2,"/"),2)
=REGEXREPLACE(TO_TEXT(B2),"\D.+$","")/REGEXREPLACE(TO_TEXT(B2),"\d.+/","")
Note* – If you want to calculate the columns or rows of numbers to find the total in case of a sales form or sales tracking form, etc. Select all cells and at the bottom, you’ll see SUM and you’ll see the total that can you enter in any cell.
Calculate Percentage Through Array Formula
An easier workaround to the copy formula down approach is the ARRAY Formula method. It will not only automatically copy the formulas to every row of the cell but also add the formulas whenever a new quiz response is submitted.
Go to cell C1 and paste the below 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),)))
The function examines the index of the row, and if it corresponds to the first row, it inserts the column title. Then, it verifies whether column B contains a score value, and if so, computes the percentage score.
Or,
=ARRAYFORMULA(IF(NOT(ISBLANK(B2:B)),B2:B/40,))
Note that ‘40’ in this formula is the maximum number out of which scores are given. You can modify it according to your total score.
This is how you can calculate the percentage score in Google Forms, with the help of Google Sheets. The process may look a bit long but it is not exactly so when you try it yourself. If you are knowledgeable in Google Sheets and its formulas then this process will be as smooth as a sail.
Want to create an order form for your business? Read, How Do You Create a Google Forms Order Form? (4 Easy Steps)
Method 2: Calculate Fields in Google Forms – Use Add-on
If you want to sum, multiply, divide, provide a summary, net total, etc in Google Forms itself, then there is an add-on that you can install and use for free for a limited time period (after that you’ll have to subscribe to its paid plans).
Formfacade and Scorecery (scorecery is the child product of Formfacade) are the add-ons that you can use to calculate fields in Google Forms. You can install both add-ons or just Scorecery to work calculate fields in Google Forms. Let’s quickly follow the steps.
- Install the Formfacade and Scorecery add-on from Google Marketplace. Or through Google Forms > Add-on > Install the add-ons.
- Once you’ve created the forms, add in the last Short Answer question with the name Total.
- Click on the Scorecery from the puzzle shape icon, then ‘Configure score.’ Then ‘Proceed,’
- It will take you to the Formfacade editor page, click on the gear shape icon beside the ‘total’ question.
- A settings box will open, go to Answers, and set the appearance drop-down to “Read-only.”
- Then use the calculated field to add the calculation. Then Save it.
This is how to calculate fields or a percentage score in Google Forms.
Wrap-Up
In conclusion, calculating the percentage score in Google Forms is essential for evaluating respondents’ performance and gauging the effectiveness of the assessment.
With the help of Google Sheets, calculating the percentage score can be made easy and efficient. These tools and tips, can get the most out of your Google Forms assessments and make data-driven decisions based on the results.
Note* – If you are a Google Forms user then you must try the ExtendedForms add-on. Especially if you are from education institutes that create quizzes and assessments and want to take it a notch up.
ExtendedForms is a countdown timer and proctor features add-on. Try out the free plan of the add-on in which you get both the main features.
If you like reading this, also consider reading our detailed guide on Creating Business Forms Using Google Forms (7 Simple Steps) and How to Add a Hyperlink to Google Forms? (7 Simple Steps).
