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

andy34

New Member
Joined
Jun 4, 2019
Messages
3
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


Row/ColABC
1Choices# resultStats to calc on
2wanted
31 month0.086 months or under
42 months0.176+ months to 1 year
53 months0.251+ to 2 years
64 months0.332+ to 3 years
75 months0.423+ to 4 years
86 months0.504+ to 5 years
97 months0.585+ to 6 years
108 months0.676+ or older
119 months0.75
1210 months0.83
1311 months0.92
141 year1.00
151+ year1.01
162+ years2.01
173+ years3.01
184+ years4.01
195+ years5.01
206 years or older
6.01

 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,280
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 4, 2019
Messages
3
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):
=LEFT(K6,3)
looks at Col A

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

Seems to work but probably not the best solution.

Thanks, Andy
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,280
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 4, 2019
Messages
3
Hi Norie, that worked perfectly! Jeez, one of those things where I was trying to make it more complicated than it was! Thanks! Andy
 

Watch MrExcel Video

Forum statistics

Threads
1,129,556
Messages
5,637,035
Members
416,954
Latest member
Gohar hussain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top