Google Forms to Sheets in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate Average Response Time

You have a Google Form linked to a Sheet. Column A records the timestamp of each form submission. Which formula correctly calculates the average time between submissions in minutes?

A =AVERAGE(A2:A) - AVERAGE(A1:A)

B =AVERAGE(ARRAYFORMULA((A3:A - A2:A) * 1440))

C =AVERAGE(A3:A - A2:A) * 1440

D =AVERAGE(ARRAYFORMULA(A2:A - A1:A)) * 1440

💡 Hint

Remember timestamps are in days; multiply by 1440 to convert to minutes.

Option B correctly calculates the difference between each pair of timestamps, converts days to minutes by multiplying by 1440, and then averages those differences.

Other options either average timestamps directly or use incorrect ranges causing errors or wrong results.

❓ Function Choice

intermediate

Choose the Correct Function to Count Unique Email Responses

You want to count how many unique email addresses submitted the Google Form. Emails are in column B starting from B2. Which function will give the correct count?

A =COUNTUNIQUE(B2:B)

B =COUNTA(B2:B)

C =COUNTIF(B2:B, "<>" )

D =UNIQUE(B2:B)

💡 Hint

You want to count distinct emails, not just total or non-empty.

COUNTUNIQUE counts distinct values in the range, which is what you want.

COUNTA counts all non-empty cells, COUNTIF counts cells not equal to empty but includes duplicates, UNIQUE returns a list, not a count.

❓ data_analysis

advanced

Identify the Most Common Answer in a Form Question

Column C contains answers to a multiple-choice question from the form. Which formula returns the answer choice that appears most often?

A =INDEX(C2:C, MATCH(MAX(COUNTIF(C2:C, C2:C)), COUNTIF(C2:C, C2:C), 0))

B =MODE(C2:C)

C =INDEX(C2:C, MODE(MATCH(C2:C, C2:C, 0)))

D =SORT(C2:C, COUNTIF(C2:C, C2:C), FALSE)

💡 Hint

Use MATCH and MODE together to find the most frequent text value.

Option C uses MATCH to convert text to numbers and MODE to find the most frequent number, then INDEX to get the text answer.

MODE alone (A) works only for numbers, C is incorrect formula syntax, D sorts but does not return the most common answer.

🎯 Scenario

advanced

Filter Form Responses Submitted in the Last 7 Days

You want to create a dynamic list showing only form responses submitted in the last 7 days. The timestamps are in column A starting from A2. Which formula achieves this?

A =QUERY(A2:C, "SELECT * WHERE A >= date '" & TEXT(TODAY()-7, "yyyy-MM-dd") & "'")

B =FILTER(A2:C, A2:A > NOW() - 7)

C =FILTER(A2:C, A2:A >= TODAY() - 7)

D =QUERY(A2:C, "SELECT * WHERE A > date '" & TEXT(NOW()-7, "yyyy-MM-dd") & "'")

💡 Hint

QUERY requires date format in yyyy-MM-dd and TODAY() is better for whole days than NOW().

Option A correctly uses QUERY with a date filter for the last 7 days using TODAY() minus 7.

Option A works but includes partial rows and may error if columns mismatch, B and D use NOW() which includes time and may exclude some rows unintentionally.

🧠 Conceptual

expert

Understanding Form Response Sheet Auto-Updates

When a Google Form is linked to a Sheet, which statement best describes how new responses appear in the Sheet?

A New responses overwrite the oldest rows in the Sheet to save space.

B Responses are added as new columns to the right of existing data.

C You must manually refresh the Sheet to see new responses after form submission.

D New responses are appended as new rows at the bottom of the existing data automatically.

💡 Hint

Think about how data grows over time in a form response sheet.

Google Forms automatically add new responses as new rows at the bottom of the linked Sheet.

They do not overwrite old data, require manual refresh, or add columns.