I need help building a complex questionaire in Excel. Questions are arranged in categories.
A simple question might be placed in a row cell with a YES/No answer next to it. If the user selects No the score is 0, if the user hits yes they score a 1 - Simples (even I can do this) but here is where it gets complicated.
A more complicated question might have the answer options Internal/External.
The answer has a score still but this time the spreadsheet asks new questions based on the result.
Category 1 - Pollution
Question 1 - Are you male or female- Score 0 or 0
Question 2 - Do you have a car ? - Yes/No Score 5 for yes and 0 for No
If Answer 2 = Yes then ask:
Question 2a- Is It Diesel Or Petrol Or LPG - Score 4 for Diesel 2 For Petrol 1 For LPG
If Answer 2a = LPG Then ask:
Question 2aa - Is It Single Or Multipoint Injection ? Score 2 For Single & 1 For Multipoint
If Answer 2 = No Then Ask:
Question 2b - Do you have a Motorbike - Yes/No 1 For yes. 0 For No.
Question 3 ....etc
I need to know the sum of all the scores for each question.
So in out example Question 1 always has a score of 0
Question 2 could have a score of 0 for (no car and no bike), a score of 7 for a multipoint LPG car, or up to 9 for a diesel. I will then sum the questions in each category and draw a graph.
There are several ways to solve this problem. I am open to suggestions.