The Raw Data is a survey I was given, responses are indicated as 1-6, 1 is the equivalent of an A on a scantron.
For the majority of the data I was able to use the following formula:
=INDEX(Raw!$X5:$AC5,MATCH(FALSE,ISBLANK(Raw!$X5:$AC5),0))
The above formula is perfect for finding the respondent's answer if they only selected one answer.
My issue is for questions that have multiple right answers such as Check all the apply (and the right answer would be B, C, D).
Example would be Question 6
answer for Question 6 is B, C, D and I am trying to code it that all three answers must correct
I am trying to code this as:
1 for completely correct
0 for not correct
* for all empty cells
As of right now I have two formulas that work
=IF(AND(NOT(ISBLANK(Raw!$AF4)),NOT(ISBLANK(Raw!$AG4)),NOT(ISBLANK(Raw!$AH4))),"1")
The above evaluates the three correct spaces are filled and are given one
does not evaluate all three spaces are empty then * or
else as 0
and
=IF(SUM(Raw!$AF5:$AH5)=9,"1","0")
The above formula works great except it doesn't output a * or N/A for the data with no responses, which is what I want for cells G7, G10.
Any Suggestions
KAMERLOCHER-RAWDATA.xls
Bookmarks