Help

Re: Converting word responses into intergers

Solved
Jump to Solution
460 4
cancel
Showing results for 
Search instead for 
Did you mean: 
JustinTracey
4 - Data Explorer
4 - Data Explorer

Hi everyone, I am new to the Airtable and I'm excited to be part of this community. May I apologise in advance if I don't use the correct technical terminology.  I design surveys, impact assessments and evaluation tools for a criminal justice charity. 

I have created a survey using 'Forms' and in one section I have 10 questions that require word responses. For example one of my questions is 'When I am confronted with a problem i can usually find a solution'

The multiple choice response can be 'Not at all true' [=1]. Barely true [=2]. Moderately True [=3]. Exactly True [=4]

I need to create a formula for a separate column that will tally up all the numerical values and give me the overall score. 

Any advice on how I write this formula will be greatly appreciated. 

Cheers Justin  

 

 

3 Solutions

Accepted Solutions
Sistema_Aotearo
8 - Airtable Astronomer
8 - Airtable Astronomer

You're looking for a "Switch" formula.

SWITCH(
{Field Name},
"Not at all true", "1",
"Barely true", "2",
"Moderately True", "3",
"Exactly True", "4"
)
  Is this what you're looking for?

See Solution in Thread

Adding on to Sistema's solution, after you create one of these formula fields with a SWITCH per answer field you have, you'll create a final formula field to sum them all up that looks something like:

 

SUM({Field 1}, {Field 2}), {Field 3})

 

See Solution in Thread

ScottWorld
18 - Pluto
18 - Pluto

@JustinTracey 

Very similar to a spreadsheet, you would just add up number fields like this:

{Field 1} + {Field 2} + {Field 3} + {Field 4}

You can learn more about Airtable's formulas in this Airtable support article on formulas:

https://support.airtable.com//docs/formula-field-reference

 

See Solution in Thread

7 Replies 7
Sistema_Aotearo
8 - Airtable Astronomer
8 - Airtable Astronomer

You're looking for a "Switch" formula.

SWITCH(
{Field Name},
"Not at all true", "1",
"Barely true", "2",
"Moderately True", "3",
"Exactly True", "4"
)
  Is this what you're looking for?

Adding on to Sistema's solution, after you create one of these formula fields with a SWITCH per answer field you have, you'll create a final formula field to sum them all up that looks something like:

 

SUM({Field 1}, {Field 2}), {Field 3})

 

ScottWorld
18 - Pluto
18 - Pluto

@JustinTracey 

Very similar to a spreadsheet, you would just add up number fields like this:

{Field 1} + {Field 2} + {Field 3} + {Field 4}

You can learn more about Airtable's formulas in this Airtable support article on formulas:

https://support.airtable.com//docs/formula-field-reference

 

Thats brilliant Scott- thank you

Hi Scot, 

I wanted to get the sum total of all the values in each fields represented in one column as one final score. I'm not sure even if this is even possible in air table. Perhaps I am being a bit too ambitiious. so this is the formula I created: 

 

SWITCH(
{I can always manage to solve difficult problems if I try hard enough}+{If someone disagrees with me I can find the means and ways to get what I want}+{It is easy for me to stick to my aims and accomplish my goals.}+{It is easy for me to stick to my aims and accomplish my goals.}+{I am confident that i could deal efficiently with unexpected events}+{I know how how to handle unforeseen situations}+{I can solve most problems if I invest the necessary effort}+{I can remain calm when facing difficulties because I can rely on my coping abilities.}+{When confronted with a problem, I can usually find several solutions.}+{If I am in trouble, I can usually think of a solution}+{I can usually handle whatever comes my way},
"Not at all true", "1",
"Barely true", "2",
"Moderately True", "3",
"Exactly True", "4"
)
 
Needless to say it didnt work. I did try it on just one field and it worked well, except some the of the rows had missing values. However, I just thought there was a quicker more efficient way to do it.
 
 

By the way i just want to add i was never taught at college how to use spreadsheets so I am a real beginner who has no idea about how formula work. By the way i did read the recommended article and video but even that expected a certain rudimentary understanding of excel formulas. 

Ahh yeah, the SWITCH can't be used like that I'm afraid.  Did you manage to figure out why some of the rows had missing values?  I'm wondering if the text values might have trailing spaces or something, which might cause the formula to output a blank

For example, the text might be "Not at all true " instead of "Not at all true"