Tips and Tricks for Google Forms for Summer Reading Tracking May 2019
Libraries track summer reading in so many ways, that we can't give instructions to each and every format here. If you need help setting up something that isn't addressed here, email marcia.literati@tenafly.bccls.org. We will follow up with a Tech Talk in early August to help you tally the data you've collected.
Data Collection
You can use google forms to simply track the number of people signing up for summer reading. The form responses will generate a spreadsheet which you can use to track data. Here are two examples:
Once the names have been added to the spreadsheet you can simply add columns to the spreadsheet to “hand enter” data. Alternately you can use a google form to enter data and then automatically total data categories.
In either case,

Think about what data you will need at the end of the program. Your endofsummer data analysis needs should dictate how you set up your google form.

Collect the fewest number of data categories as possible. For example, if you only really need to know the total number of children, teens and adults that participate, create one multiplechoice question with 3 answers only and don't ask other questions such as school or grade.
Do you need someone's phone number and email address or can you simply enter a library barcode number? Do you really need any of this information?
Example: Use multiple choice question  Age group ◯ Children (ages 0  12) ◯ Teen (ages 1318) ◯ Adult (over 18)

Try to limit the number of fields in which you enter free form text. Instead use multiple choice questions whenever possible.

For example, if you require a name entry, google will recognize MARCIA, marcia, Marcia as different values.

From the standpoint of data collection, assigning a unique number to each participant limits possible data entry errors (of course you then need a way to keep track of the numbers you've assigned.)

A library card number (if all readers have one) might be a good option. This also gives you the advantage of being able to scan the barcode which creates a smaller number of data entry errors than typing.

If you require the entry to be a number and use response validation, you can limit some errors (see question 9 in sample google form).

For example, if you track minutes but count everything under 15 minutes as 15 minutes, use “number”, “greater than or equal to” 15 to validate your data.
Data Analysis (use the spreadsheet generated by your google form)
You can manually enter additional data into the spreadsheet you originally generated when participants signed up.
For example, you can manually add a column for “# books read” and you simply enter the total at the end of the program. Or add a column for each week of the program and enter books read as reported by the participants, in each column.
Or you can use functions to automate the data analysis.
simple sum or total:
=sum(A1:A10)
Sum of values with a specific criteria (example: the total of all books read by grade)
=sumif(A1:A10, “Kindergarten”, B1:B10)

A1:A10 represents the column in which you identify the grade

the word in parenthesis is the actual value

B1:B10 represents the column in which has the number of books entered
Sum of values with 2 specific criteria (example: the total of all books read by grade for each school)
=sumifs(E2:E23, C2:C23,"Kindergarten", D2:D23, "Mackay")

E2:E23 represents the column that you are summing

C2:C23 represents the first criteria

D2:D23 represents the second criteria
When you copy/paste formulas into new cells, google figures that you want the range of values as shown by A1:A10 to change. For example, if A1:A10 total is in cell B11 and I then copy the formula to cell B12, google will think it should total up the sum from cells A2:A11.There are times when you don't want the values to change.
If you want the values to be the same no matter where you paste the information use
A$1:A$10 to keep the rows from changing when you copy to another cell
Count all values:
=CountA (A1:A10)
List of unique values:
=Unique (A1:A10)
Use Query to find totals by unique values.
=query(A1:B22 , "select A, sum (B) group by A")

Select A = tells google to look for values in column A

Sum (B) = tells google add up the values in column B

Group by A = show me the totals from column B for each unique value in column A
