yogi_Compute Scores Row By Row Within The Form Responses Sheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #738   Sep 07, 2012     www.energyefficientbuild.com.

user chlordk said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/how-do-i/desktop/d3CL12H3HpM)
Result from form post can not be calculated automatically, multiple choice questionnaire
I have a multiple choice questionnaire like match these 5 capitals to these 5 countries. After the posting I want the user to see his own result and also all others result.

I have made with one sheet for the posting. This comes by default when using a form. Then I have added a new sheet where I have pre-made the result for a number of answers. So if one add an answer #1 it will be in row #2. On the result page I then match the answer with heading of the column, like this =Quiz!B3=B$1 - so if the answer in Quiz!B3 is the same as in Result!B$1, then I get a TRUE. After that I can count all the TRUE's and then get the score.

The problem is that the result page loose the reference to say cell Quiz!A2. The cell Result!A2 is supposed to point Quiz!A2 but after I post when the cell Result!A2 is pointing at Quiz!A3.

The first result is located in Result!W2. Result!W3 is my test to see if everything is correct.

Here is the form:

https://docs.google.com/spreadsheet/viewform?formkey=dFduWE04WE1CVHdWdDhLUmQ5c3dRU1E6MQ#gid=1

Answers and result page:

https://docs.google.com/spreadsheet/ccc?key=0AjpXtOgxgsCvdFduWE04WE1CVHdWdDhLUmQ5c3dRU1E

Actually I would like to one big block with all 20 countries but Google can only handle 5 in a block. Please don't tell I made it all wrong as it took quite some time to make it this way.

Feel free to try the test. I warn you - the average on correct answers are about 6 of 20.

-----------------------------------------------------------------------------------------------

in the following I have presented a solution wherein I have computed the scores row by row within the so called Form responses sheet

[and if you want to use a separate Result sheet as you currently have in your set up, you can use the following array formula in cell W2: =ArrayFormula(mmult(B2:U*1,transpose(column(B2:U)^0)))]