1. Create a new Google Form
For sorting purposes create separate questions for the first and last names
Use only "Multiple Choice", "Choose from a List" and "Checkboxes" and be sure there is only one correct answer
As long as you limit yourself to the question types listed above, you can have as many different questions as you'd like.2. Go to the live Form and complete it with the correct answers3. Return to Google Docs and open the Spreadsheet view of your Form
Copy and paste the correct answers into the blank columns in Row 2
Label the last Column “Student Average”4. To write the formula, you need to use an IF/THEN statement, so if you are looking at the data collected in Column D to match the data you input in Column F, your formula would look like this:
=if(D3=F1,100,0)
things to think about
if it = F1 = 100 --> they got the answer correct
if it ≠ F1 = 0 --> they got the answer wrong
This example is for Row 3
5. Since you will want the formula to repeat for every student that completes the Form, you need to add the “$” in front of the F1 to keep that cell a constant
=if(D3=$F$1,100,0)
6. To calculate your Student Average you will enter the following formula:
=average(F3:G3)
":"= to --> so adjust the cells as needed
(You can then sort by class average)
Note: You must hit the ESC (Escape) button to leave a cell rather than clicking on another cell like you would in Excel.
7. If you would like to set your spreadsheet so that each cell that contains a correct answer will turn GREEN and each cell with a wrong answer will turn RED, simple right-click on each column you want to set and select "change color with rules".*
If the text = 100 then the background is Green
If the text = 0 then the background in Red
*Make sure to click "add another rule."
Notes from group:
Double click on a cell to see the formula in that cell.
