Google Form Quizzes - Find Max Score - Formulas

Sometimes a little grade candy is just the motivation teachers need to get students to learn factual information.  Factual information can be easily assessed with a Google Form.  But what if you want to find the max score for each student?  We some finessing and two slick formulas, we can create our list.  Using GradeTransferer we can then upload our marks into our gradebook.  I'll be using Aspen.  

If you try this, just make sure you have set your quiz to collect the email addresses of the users.  I also recommend having a dropdown with all the student names or two text fields where students can type in their first and last names.

Problem:

Google Classroom doesn't accept the importation of marks if a user has taken a Google Form quiz more than once.  So, we need a way to find just the highest mark to update our gradebook.  With your Google Form set to allow the users to take the quiz as many times as possible, lets get started.

Form should not limit the number of responses.

Solution & Skills:

We will construct two solutions, one that only uses formula, and another that will be based off of script, so that you can reuse the formulas whenever you want.

Skills:

✔ Apply Query Function to pull max score;

✔ Utilize vLookup to find names based off of unique identifiers;

✔ Evaluate the benefits of arrayFormula.

Formula Solution:

Within the edit mode of your Google Form, select responses and ensure you have opened the responses in the spreadsheet.  This is where we will work our magic.

Your spreadsheet should have one tab with a purple form icon next to it. It may read some like, "Form Responses 1".  You can rename it if you want, but it is not necessary.

Click on Sheet1 or add a new sheet by click on the "+" button in the lower left hand corner of the Google Sheet.

In the new sheet, go to cell A1. This is where we will place our query function.  Query functions are the Swiss Army knives of spreadsheet functions.  It can filter, group, and provide arithmetic functions. 

Components of the query function:

Here is the query function we will be using:

=query('Form Responses 1'!B2:E,"select B, max(C) group by B",-1)

The 'Form Responses 1'!B2:E tells the source of the data, starting at cell B2 through all of E. Notice, we aren't grabbing the headers.  E represents the last column in my data that has student's name.

The "select B, max(C) group by B" tells Google Sheets to get Column B and the highest score found in column C.  If you want, could add count(B) to determine how many times the student attempted the quiz.  

The group by B is telling the query to compare the scores only where there are matching users from Column B.  It is important to use a unique identifier when looking to group information.  Column B, for my data, houses the student email addresses. No two students have the same email address, so this identifier is ideal for comparing students.

Now, let's setup the next formula to get the first name and last name and display the score.

=arrayFormula(iferror(if(ISBLANK($A$1:$A),"",split(VLOOKUP($A$1:$A,'Form Responses 1'!$B$2:$F,3,false)&" "&VLOOKUP($A$1:$A,'Form Responses 1'!$B$2:$F,4,false)&"|"&$B$1:$B,"|")),""))

Confused yet?  Let's break it down.  We essentially want lookup the email to find the last name and the first name.  Then, we want to join them together.  We will also join the score.  Then, we will split the score off to fill the next column.  Finally, we need to keep the data looking clean, catch errors, and use the formula for the entire column.  Sound complicated?  Let's focus on one step at a time.

Step 1: vLookup

=VLOOKUP($A$1:$A,'Form Responses 1'!$B$2:$F,3,false)

vLookup stands for vertical lookup and will find the first occurrence of the key being searched.  We want to use for our search key the unique identifier (email address) in column A, but we are going to say the key is from $A$1:$A.  Placing the dollar sign (pressing F4) will lock the notation.  

The data to search through must start with column we want to search for a match. Google Form has the email addresses saved in the second column, or the "B" column.  We note the data as: 

'Form Responses 1'!$B$2:$F 

'Form Responses 1'! is the absolute sheet where the data is found.  $B$2:$F is the fixed range of the data we want to search.  I have omitted the first row from my search, but I am searching email column (B) and accessing the score, first name, and last name data entries.  

The number "3" tells Google Sheets to lookup the third column index from my range of data.  For my data, that would be the first name for my student.  If you run this formula, you should get the first name of the student.

Step 2: Concatenate 2 vLookup Results 

Concatenate means join, and while there is a Join function, I rarely use it unless I have lots of cell data that needs to be put together.  We looked up the first name, now we need to look up the last name and put them together.  Copy your vLookup formula.  After the original formula, type the ampersand symbol (&), a double quote ("), space, end double quote ("), and another ampersand. It should look like this... &" "&. Finally, paste the vLookup we had previously copied and change the index number (3) to a 4 or whatever column holds last name.  Your formula should look like this:

=VLOOKUP($A$1:$A,'Form Responses 1'!$B$2:$F,3,false)&" "&VLOOKUP($A$1:$A,'Form Responses 1'!$B$2:$F,4,false)

Step 3: Concatenate the Scores

We already pulled our max scores with our query function.  Now, we want to join the score with a unique symbol that should not appear any of our names...the pipe symbol "|".  The type symbol is located above your "enter" key and is typically accessed when you hold the shift key.  

At the end of our previous formula, add the ampersand symbol (&), double quote ("), pipe symbol (|), end double quotes (") and another ampersand.  It should look like this: &"|"&.  Then type the range, B1:B and press F4 to lock the notation ($B$1:$B). The formula should now look like this:

=VLOOKUP($A$1:$A,'Form Responses 1'!$B$2:$F,3,false)&" "&VLOOKUP($A$1:$A,'Form Responses 1'!$B$2:$F,4,false)&"|"&B1:B

Result: = First Last|#score

Step 4: Split the Score

Now that we have an unusual delimitator, demarcating the end of the name and the beginning of the score, we can split the score off so that it is in its own cell.

After the equal sign, but before the first vLookup, type "split(". Go to the end of the formula, type comma, double quotes, pipe, double quotes, end parentheses.  It should look like this: ,"|").  Your entire formula should now look like this:

=split(VLOOKUP($A$1:$A,'Form Responses 1'!$B$2:$F,3,false)&" "&VLOOKUP($A$1:$A,'Form Responses 1'!$B$2:$F,4,false)&"|"&$B$1:$B,"|")

  

Step 5:  If & IsBlank

Nothing is worse than getting an error message when dragging your formulas to cells that won't be calculated. So, let's fix that.

We want to use a logic statement: If the reference cell is blank, keep this cell blank, else run my slip and vlookups.  Now, lets put that phrase into the formula.

After the equal, but before the split, type: if(isblank($A$1:$A),"",

At the end of the formula add the closed parentheses: )

Your formula should now look like this:

=if(isblank($A$1:$A),"",split(VLOOKUP($A$1:$A,'Form Responses 1'!$B$2:$F,3,false)&" "&VLOOKUP($A$1:$A,'Form Responses 1'!$B$2:$F,4,false)&"|"&$B$1:$B,"|"))

Step 6: Error Check

We need one last error check, because cell A1 has the query function and our new formula will read A1 and kick out an error.

After the equal, but before the if statement, type: iferror(

At the end of the formula, place a comma, two double quotes, and closed parentheses: ,"")

Step 7: Automatically Populate the Formula

Gone are the days of dragging the formula down the entire sheet.  We want our sheet to be dynamic and update itself!  To do this, we are going to use the arrayFormula function.  This beautiful tool will apply the formula to the entire column.

After the equal, but before the iferror type: arrayformula(

At the end of the formula add the closed parentheses: )

Your finished formula should appear like this:

=arrayformula(iferror(if(isblank($A$1:$A),"",split(VLOOKUP($A$1:$A,'Form Responses 1'!$B$2:$F,3,false)&" "&VLOOKUP($A$1:$A,'Form Responses 1'!$B$2:$F,4,false)&"|"&$B$1:$B,"|")),""))

If you get a #REF! (Reference Error) it is likely due to a formula or text being in the column.  Clear out any other data in the column and you should be now notice the first and last names in one column and the scores next to the name.

What to do now?

Depending on your SIS/Gradebook, you can probably upload the data to minimize hand entry.  However, I recently started using GradeTransferer. No, I'm not getting an incentive to mention this tool.  Copy the last two columns we just created (First and Last Names, Score), use Grade Transferer, and paste the marks into your gradebook. The process is quick and accurate, saving hours when considering the cumulative minutes required to hand enter grades from classroom to the SIS gradebook.

Script Solution:

In the next blog, I'll show you have to use the formulas Google Script.  I'll show you how to create a custom toolbar add-on so that you can reuse the same spreadsheet multiple times to effortlessly insert the formulas and get your scores.