( by Jake Probst )
Hello,
I am doing some monitoring with a student using this form:
The results end up like this:
What I would like to do now, is create a separate sheet to graph each column over time. In order to do this, I was thinking I would use a conversion sheet that uses IF THAN statements to convert the results to numerical values.
This is one of the things I have tried in order to accomplish this:
=IF(OR('Form Responses'!B2="1 - Less than 1/2 of the homework was completed."),'Form Responses'!B2=""1", IF(OR('Form Responses'!B2="3 - 3/4 or more of the homework was completed."),'Form Responses'!B2=""2", IF(OR('Form Responses'!B2="3 - 3/4 or more of the homework was completed."),'Form Responses'!B2=""3", IF(OR('Form Responses'!B2="4 - All of the homework was completed."),'Form Responses'!B2=""4"))
Unfortunately, I am getting a parse error. Any ideas?
Also, If you see a cleaner way to accomplish this, I am certainly open to it.
Thank you!
Solution:
Have a look at the following screenshot of "Form Reponses":
Have a look at the following screenshot of "Sheet2":
In the above "Sheet2" I have the following formula in Cell A1:
=Arrayformula('Form Responses'!A:A)
and the following formula in Cell B1:
=Arrayformula(if(row(A:A)=1;"Homework Completion [Points Earned]";iferror(match('Form Responses'!B:B;{"1 - Less than 1/2 of the homework was completed.";"2 - At least 1/2 of the homework was completed.";"3 - 3/4 or more of the homework was completed.";"4 - All of the homework was completed."};0))))
I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html
When I Move the formula to C1, I tried making the following changes, but came up with a parse error. Any ideas?
ReplyDelete=Arrayformula(if(row(A:A)=1;"Active Listening/ Participation [Points Earned]";iferror(match('Form Responses'!C:C;{"1 - I was distracted by "in the brain" or "out of the brain" thoughts during my core classes.";"2 - I was distracted by "in the brain" or "out of the brain" thoughts during 2 of my core classes.";"3. I provided a topic related comment or question during 3 or my core classes.";"4 - I provided an topic related comment or question during each core classes."};0))))
Try the following formula in Cell C1:
Delete=Arrayformula(if(row(A:A)=1;"Active Listening/ Participation [Points Earned]";iferror(match('Form Responses'!C:C;{"1 - I was distracted by "&char(34)&"in the brain"&char(34)&" or "&char(34)&"out of the brain"&char(34)&" thoughts during my core classes.";"2 - I was distracted by "&char(34)&"in the brain"&char(34)&" or "&char(34)&"out of the brain"&char(34)&" thoughts during 2 of my core classes.";"3. I provided a topic related comment or question during 3 or my core classes.";"4 - I provided an topic related comment or question during each core classes."};0))))