yogi_Compute Student Score For Each Quiz And Then SumUp The Scores For All The Quizes For Each Student

                                          Google Spreadsheet   Post  #957

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jan 06, 2013

user Neal Boocock said:(http://productforums.google.com/forum/?zx=6xwd73m1l93e#!category-topic/docs/spreadsheets/xAxD8urYDqo)
Need help with an array formula
Hello,

Here is my scenario, I have a quiz that is administered through a google form and produces results to a quiz results page. Also in the quiz results page is the quiz key. I am attempting to grade the quiz with an array formula on my grade book where I need to count the number of times a students answer is the same as the key and then multiply the count by 10 because each correct answer is worth 10points.  

Currently I can get the formula to work when "I" specify which rows to compare to the key, but I need the formula to do a dynamic match based on the students name and then calculate grade based on the row matched.

Currently I am using the formula below in my grade book but I have to change the row reference depending on the order in which the students complete their quiz, for example row 3 in the grade book may be student#2, but in the quiz results student#1 may be in row 6 depending on the order in which student#1 finished, and then on the next quiz the students may finish in a different order, so I have to change all the references again.

=arrayformula(sum(if('Quiz Results'!5:5='Quiz Results'!2:2,1,0)))*10

'Quiz Results'!5:5 is Student#1's answers and 'Quiz Results'!2:2 is the Key.  I need the current reference to row 5 to be dynamic based on the students name

Here is the link to my example:

https://docs.google.com/spreadsheet/ccc?key=0AvRJForx8LEndDF1bTNYbXRWMkxaUVA2NklyWGhDNUE#gid=0

I hope I have provided enough information.  Thanks for any help you can provide.

-Neal

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

following is a solution to the problem -- I have used VMERGE custom written function by Ahab (available in script gallery from within Google spreadsheet)