Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Response are automatically added to the sheet "Form Responses". I want to apply certain formulas in a sheet called "Student Answers". However, "Student Answers" doesn't auto-update from the submitted responses. Here are the formulas I am trying to use:

='Form Responses'!A1 etc. =IF('Form Responses'!E6='Answers'!$E$2,1,0) "Answers" is another sheet with an answer key =SUM(D5:I5) this sums the numbers given in the previous formula.

Basically, I have created a quiz, and I want to grade the quiz on a separate sheet. Here is the Sheet in question: https://docs.google.com/spreadsheet/ccc?key=0AqvTp4ajjRSUdGlQNDV5cUd4TVFmeFB3TndJeTBLblE&usp=sharing

Thanks for any help.

share|improve this question
up vote 1 down vote accepted

The solution was to use =ARRAYFORMULA. Here are my working examples:

  • =ArrayFormula('Student Submissions'!A:C) for basic copying of columns

  • =ARRAYFORMULA(If('Student Submissions'!D3:D='Student Submissions'!D3,1,0)) for IF statements

  • =ArrayFormula(mmult(D3:I;TRANSPOSE(sign(column(D3:I))))) for SUM

  • =ARRAYFORMULA(J3:J/6*100) for AVERAGE and percent

Note: I found these answers in the Google Product forums but cannot find the link again.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.