Converting word responses into intergers

[JustinTracey]

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  

[Sistema_Aotearo · Accepted solution]

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?

[TheTimeSavingCo · Accepted solution]

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 · Accepted solution]

@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

[JustinTracey]

Thats brilliant Scott- thank you

[JustinTracey]

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.

[JustinTracey]

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. 

[TheTimeSavingCo]

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"