Google Form to Google Sheets - how do I get Age responses with text into numbers?

[andy34]

Hello,
I have a Google Form which will be used to register animals for a spay & neuter clinic. One of the questions is "Age".

I have multiple choices in Col A which include text. I want those choices to turn into numbers in Col B. I intend to report stats based on age groups in Col C.

1) Is there an easier way to set up the form so this gets calculated automatically? (English is a 2nd language here, so I can't rely on person entering too much)

2) Assuming no to #1 , I am bringing the responses into a Google Sheet where I can do formulas. What formula(s) do I use to get the responses from Col A into numbers like Col B?

Thanks, Andy


[TABLE="width: 428"]
[TR]
[TD]Row/Col[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Choices[/TD]
[TD]# result[/TD]
[TD]Stats to calc on[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]wanted[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1 month[/TD]
[TD]0.08[/TD]
[TD]6 months or under[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2 months[/TD]
[TD]0.17[/TD]
[TD]6+ months to 1 year[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3 months[/TD]
[TD]0.25[/TD]
[TD]1+ to 2 years[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]4 months[/TD]
[TD]0.33[/TD]
[TD]2+ to 3 years[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]5 months[/TD]
[TD]0.42[/TD]
[TD]3+ to 4 years[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]6 months[/TD]
[TD]0.50[/TD]
[TD]4+ to 5 years[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]7 months[/TD]
[TD]0.58[/TD]
[TD]5+ to 6 years[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]8 months[/TD]
[TD]0.67[/TD]
[TD]6+ or older[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]9 months[/TD]
[TD]0.75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]10 months[/TD]
[TD]0.83[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]11 months[/TD]
[TD]0.92[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]1 year[/TD]
[TD]1.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]1+ year[/TD]
[TD]1.01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]2+ years[/TD]
[TD]2.01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]3+ years[/TD]
[TD]3.01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]4+ years[/TD]
[TD]4.01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]5+ years[/TD]
[TD]5.01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]6 years or older[/TD]
[TD="align: left"]

6.01

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: left"]

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[/TR]
[/TABLE]

 

[Norie]

Andy

Why not set a table up with the choices in one column and the numeric value in another?

That could then be used in a lookup to return the required values from the user input.

You might even be able to use the first column in the table in your form

 

[andy34]

Hi Norie,

Thanks for the response. I did a check on adding tables to Google Forms, and it doesn't seem like that is available? (other than going out to 3rd party software which I'd prefer not to do).

Also, there are another 10+ response areas that I'd like to keep in order (i.e. owner, city, email, animal type, gender etc).

I did the following (long, not shortcuts):
[TABLE="width: 72"]
[TR]
[TD="class: xl65, width: 72"]=LEFT(K6,3) [/TD]
[/TR]
[/TABLE]
looks at Col A

[TABLE="width: 72"]
[TR]
[TD="class: xl65, width: 72"]=IF(Q6="1 m",0.08,IF(Q6="2 m",0.17,IF(Q6="3 m","0.24",IF(Q6="4 m",0.33,IF(Q6="5 m",0.42,IF(Q6="6 m",0.5,IF(Q6="7 m",0.58,IF(Q6="8 m",0.67,IF(Q6="9 m",0.75,IF(Q6="10 ",0.83,IF(Q6="11 ",0.92,IF(Q6="1 y",1,IF(Q6="1+ ",1.01,IF(Q6="2+ ",2.01,IF(Q6="3+ ",3.01,IF(Q6="4+ ",4.01,IF(Q6="5+ ",5.01,IF(Q6="6 y",6.01,""))))))))))))))))))[/TD]
[/TR]
[/TABLE]

Seems to work but probably not the best solution.

Thanks, Andy

 

[Norie]

Andy

I thought you were using Google Sheets as well as Forms?

My suggestion is to set up a table on a sheet in Google Sheets and then use something like VLOOKUP to return the appropriate value based on user input.

For example, let's say you've set up a table on Sheet1 with the possible choices in column A and the corresponding values in column B.

You could then use a formula like this:

=VLOOKUP(K6, Sheet1!A:B, 2, 0)

 

[andy34]

Hi Norie, that worked perfectly! Jeez, one of those things where I was trying to make it more complicated than it was! Thanks! Andy