Friday, October 18, 2013

Converting Text responses to numerical values

Question:

( 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 hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


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 

Thanks,

2 comments:

  1. When I Move the formula to C1, I tried making the following changes, but came up with a parse error. Any ideas?

    =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))))

    ReplyDelete
    Replies
    1. Try the following formula in Cell C1:
      =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))))

      Delete