Formula for changing words to numbers

Claire Diener

Hi,

I've created a survey with a likert scale, Rarely, Sometimes, Often, Very Often, Always, which is captured as options within one column, and need a formula that when someone enters Rarely in the survey, a separate column in the sheet shows a 1, Sometimes would should a 2, Often 3, Very Often 4, and Always 5. I then need to take these numbers and get a full average.

What is the formula I can use for the column to turn the words into numbers?

Thanks!

Atus Bartal

Hi Claire,

 

it would be like this (any other entries result in 99): 

=IF(Column1 = "Rarely", 1, IF(Column1 = "Sometimes", 2, IF(Column1 = "Often", 3, IF(Column1 = "Very Often", 4, IF(Column1 = "Always", 5, 99)))))

 

For more information, check out the "nested if()" formula at http://help.smartsheet.com/customer/portal/articles/775363#logic

 

Atus

 

Travis

Check out my post here: https://community.smartsheet.com/discussion/feature-request-weighted-options-dropdown-list#comment-6645

Claire Diener

Thanks, i can't seem to get either of those solutions to work. I tried changing the column name and also the name of the column in the equations but no dice. I've attached a screen shot ... any thoughts?

KrisWalsh

Here are some lessons I have learned...  Hope they help.

I put all of these formulas in a sheet for you.  (:

Words into Numbers

 

1. 1. Formula Brackets -  [Column Name] - before & after the column name in a formula took me a while to get. They are required when your column name has a space in it or ends in a number.
2. Column Name: Likert
3. Can be used in a formula like this: =IF(Likert1 = "Often", 3, 99)
4. Column Name: Likert One
5. Must use brackets in a formula like this: =IF([Likert One]1 = "Often", 3, 99)

 

It can get confusing...  

 

1. 2. Getting around long complex formulas because of long column names.

- Smartsheet will change your formulas to reflect updated column names.

- Therefore, when I first create my formulas, I temporarily change my reference column name to something SUPER SIMPLE, like "X", then change it back when I'm done.

Example from your picture: 

Change "Inclusion, Collaboration and Teamwork 1" to "X".

Formula Result in Column "#Value" Row 1:

=IF(X1 = "Rarely", 1, IF(X1 = "Sometimes", 2, IF(X1 = "Often", 3, IF(X1 = "Very Often", 4, IF(X1 = "Always", 5, 99)))))

 

Make sure it's working the way you want, then change the column name back to "Inclusion, Collaboration and Teamwork 1".

When you go back & look at your formula, you'll notice it changed to this:

=IF([Inclusion, Collaboration and Teamwork 1]1 = "Rarely", 1, IF([Inclusion, Collaboration and Teamwork 1]1 = "Sometimes", 2, IF([Inclusion, Collaboration and Teamwork 1]1 = "Often", 3, IF([Inclusion, Collaboration and Teamwork 1]1 = "Very Often", 4, IF([Inclusion, Collaboration and Teamwork 1]1 = "Always", 5, 99)))))

 

 

1. Extra Credit: Absolute References to Columns (Not Rows). 
2. - This will save you if you (or someone else) moves your columns around.
3. - Here are the formulas above with Absolute Reference to Column:
4. =IF($X1 = "Rarely", 1, IF($X1 = "Sometimes", 2, IF($X1 = "Often", 3, IF($X1 = "Very Often", 4, IF($X1 = "Always", 5, 99)))))
6. Change the column name and you get:

=IF($[Inclusion, Collaboration and Teamwork 1]1 = "Rarely", 1, IF($[Inclusion, Collaboration and Teamwork 1]1 = "Sometimes", 2, IF($[Inclusion, Collaboration and Teamwork 1]1 = "Often", 3, IF($[Inclusion, Collaboration and Teamwork 1]1 = "Very Often", 4, IF($[Inclusion, Collaboration and Teamwork 1]1 = "Always", 5, 99)))))

Atus Bartal

Dear Claire,

 

OK, I see. Not easy to explain... In short: my formula works for one column only, Travis's for many. 

 

1. If you want to get a numeric value for each column, you should put a "Value#" column after every "Inclusion..." column (where # is a number bw 1-7), and put "my" formula in them. Then you can make any sort of calculations with the values. As I see on the screenshot, you've started to solve the problem this way. Be sure the column names be put in [], because they have spaces (I suggest to use simpler column names). 

 

2. If you want to get only one average value in one column for the 7 "scale" columns, use Travis's formula before or after the 7 columns (take out the one called "#Value" from between the 1st and 2nd). This formula would be the following with your column and scale names (copyright/Travis ): 

=((COUNTIF([Inclusion, Collaboration and Teamwork 1]1:[Inclusion, Collaboration and Teamwork 7]1, "Always") * 5) + (COUNTIF([Inclusion, Collaboration and Teamwork 1]1:[Inclusion, Collaboration and Teamwork 7]1, "Very Often") * 4) + (COUNTIF([Inclusion, Collaboration and Teamwork 1]1:[Inclusion, Collaboration and Teamwork 7]1, "Often") * 3) + (COUNTIF([Inclusion, Collaboration and Teamwork 1]1:[Inclusion, Collaboration and Teamwork 7]1, "Sometimes") * 2) + (COUNTIF([Inclusion, Collaboration and Teamwork 1]1:[Inclusion, Collaboration and Teamwork 7]1, "Rarely") * 1)) / COUNT([Inclusion, Collaboration and Teamwork 1]1:[Inclusion, Collaboration and Teamwork 7]1)

 

I hope it does work.

Atus

 

 

Travis

Claire - could you show us the formula you currently have that is producing the error? 

Claire Diener

I think I got it!!! I used Travis' equation for all four categories =((COUNTIF([Inclusion, Collaboration and Teamwork 1]2:[Inclusion, Collaboration and Teamwork 7]2, "Always") * 5) + (COUNTIF([Inclusion, Collaboration and Teamwork 1]2:[Inclusion, Collaboration and Teamwork 7]2, "Very Often") * 4) + (COUNTIF([Inclusion, Collaboration and Teamwork 1]2:[Inclusion, Collaboration and Teamwork 7]2, "Often") * 3) + (COUNTIF([Inclusion, Collaboration and Teamwork 1]2:[Inclusion, Collaboration and Teamwork 7]2, "Sometimes") * 2) + (COUNTIF([Inclusion, Collaboration and Teamwork 1]2:[Inclusion, Collaboration and Teamwork 7]2, "Rarely") * 1)) / COUNT([Inclusion, Collaboration and Teamwork 1]2:[Inclusion, Collaboration and Teamwork 7]2)

 

I had a little touble with the new values coming in on the wrong rows and the fomula not continuing down, but I realized that I needed to delete the rows inbetween and also have two admin rows to make sure that when items start coming into the survey the formula works. Is that right? Or is there a way to not need amin rows on the "blank" survey sheet?

Travis

I have a couple admin rows at the top which hold these calculations and new form entires are added to the bottom - which are included in my formula ranges. If you want these calculations on your sheet, I would recommend an admin row.