Monday, May 9, 2011

yogi_Make A Survey And See The Overall Score For Each Respondent

Yogi Anand, D.Eng, P.E.                                   Google Spreadsheet            

Taco Bos said: 
I'm working on a self test containing ten multiple choice questions. Each answer gives a certain score, and I'd like to automatically calculate an overall score for each respondent.
I'm thinking of the following solution:
- Replace all answers in the spreadsheet with scores
- Add up the scores for each contestant
The thing is: I don't know how I can replace certain answers for scores for each question (different questions have different scales).
I hope you can help, or maybe you see a different solution.

I use the form feature indeed, and I'm working on a translated (into Dutch) version of the Edinburgh Depression Scale:

As you can see in the PDF, each answer has a score and sometimes the first answer gives the lowest score, and with other questions the first answer gives the highest score. In Google Docs, I'd like to translate the given answers into the applicable scores without a lot of manual work for each respondent. How can I use formulas to give the right scores to the different answers? 
In the following illustration I have used three questions ... each question is a multiple choice question with 4 options. Questions 1 and 2 have a score of 0,1,2, and 3 for options 1 through 4 respectively and question 3 has score of 3,2,1, and 0 for each of the options respectively. I created the Form originally for the fields shown with Gray headers and then later on I added additional columns with brown colored background for formulas for scores by question and then a column with yellow colored header for Total Scores for each respondent.

here is the Form that was created for this illustration

and in the following I present the Summary of responses