Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Formula for changing words to numbers

Claire Diener
Claire Diener ✭
edited 12/09/19 in Archived 2016 Posts

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!

Tags:
· Share on FacebookShare on Twitter

Comments

  • Atus Bartal
    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

     

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee

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

    · Share on FacebookShare on Twitter
  • Claire Diener
    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?

    Screen Shot 2016-02-16 at 2.49.09 PM (2).png

    · Share on FacebookShare on Twitter
  • KrisWalsh
    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)))))

    · Share on FacebookShare on Twitter
  • Atus Bartal
    Atus Bartal ✭✭✭✭✭✭
    edited 02/17/16

    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 Smile): 

    =((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

     

     

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee

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

    · Share on FacebookShare on Twitter
  • Claire Diener
    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?

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee

    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. 

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories