## 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,