How do I convert a number range to a text in another column

Hi

Looking to convert a number range in a column where a number is greater then 100, return to say text in another column of Project or where less than 100 to say Small Change, any ideas?


Thanks

Tags:

Best Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi Dan,


    Try something like this:

    =if([number]@row>=100,"Large Change","Small Change")

    This is assuming your column where the numbers are is called "Number." It translates to:

    If the number is greater than or equal to zero, show "Large Change." Otherwise, small change.

    If you wanted to have three criteria, you could nest if statements:

    =if([number]@row>=100,"Large Change",if([number]@row>=50,"Medium Change","Small Change"))

    This translates to: If the number is greater than or equal to 100, show Large Change. Otherwise, if the number is greater than or equal to 50, show Medium Change. Otherwise, show Small Change.



    Best,

    Heather

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Dan,


    Absolutely! The great thing about nested IF formulas is that once one criteria is met, it stops thinking about the rest. So you could do something like this:

    =IF([Total Score]@row >= 100, "Small Change", IF([Total Score]@row >=20, "Project", "N/A"))

    This translates to:

    If the total score is greater than or equal to 100, Small Change.

    Otherwise, if the total score is greater than or equal to 20 (and less than 100 based on the first criteria), Project.

    Otherwise (which really means less than 20), N/A.


    Let me know if this makes sense.


    Best,

    Heather

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Dan,

    If you're wanting to add more options from the drop down to make it display mandatory, you'll simply add

    ,[drop down]@row = "Whatever you want it to say"

    within the OR statement. So it would look like this:

    =if(OR([drop down]@row = "Regulatory", [drop down]@row = "Legal", [drop down]@row = "Global Team", [drop down]@row = "another option", [drop down]@row = "and another one"), "Mandatory", "")

    If you want to have it display something else for other conditions, you'll need to add a nested if statement. For example, if you want it to display Mandatory for all of the categories we previously used, AND wanted it to display Optional for two other categories, it would look like this:

    =if(OR([drop down]@row = "Regulatory", [drop down]@row = "Legal", [drop down]@row = "Global Team"), "Mandatory",if(OR([drop down]@row = "Category A", [drop down]@row = "Category B"), "Optional", ""))

    This translates to: If Regulatory, Legal, OR Global Team were selected in the drop down, show "mandatory;" otherwise, If Category A OR Category B were selected in the drop down, show "optional;" otherwise, leave cell blank.


    Best,

    Heather

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi Dan,


    Try something like this:

    =if([number]@row>=100,"Large Change","Small Change")

    This is assuming your column where the numbers are is called "Number." It translates to:

    If the number is greater than or equal to zero, show "Large Change." Otherwise, small change.

    If you wanted to have three criteria, you could nest if statements:

    =if([number]@row>=100,"Large Change",if([number]@row>=50,"Medium Change","Small Change"))

    This translates to: If the number is greater than or equal to 100, show Large Change. Otherwise, if the number is greater than or equal to 50, show Medium Change. Otherwise, show Small Change.



    Best,

    Heather

  • Dan Harris
    Dan Harris ✭✭✭✭✭

    Thanks for confirming, one last question...

    I have just 2 criteria i need to pull through and have applied the principal you provided above, however i want to add a range, so the formula converts to project when the range is 20 to 100, also if the number is less than 20, converts to N/A.

    Hoping you can help?


    =IF([Total Score]@row >= 100, "Small Change", IF([Total Score]@row < 100, "Project"))


    Thanks 😊

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Dan,


    Absolutely! The great thing about nested IF formulas is that once one criteria is met, it stops thinking about the rest. So you could do something like this:

    =IF([Total Score]@row >= 100, "Small Change", IF([Total Score]@row >=20, "Project", "N/A"))

    This translates to:

    If the total score is greater than or equal to 100, Small Change.

    Otherwise, if the total score is greater than or equal to 20 (and less than 100 based on the first criteria), Project.

    Otherwise (which really means less than 20), N/A.


    Let me know if this makes sense.


    Best,

    Heather

  • Dan Harris
    Dan Harris ✭✭✭✭✭

    Thanks once again, one final question....

    I want to covert multiple texts from a drop down in one cell to convert into one text in another cell for example where cells have any of the 3 drop entries below i want convert to another cell to say Mandatory, any ideas?


    Regulatory or legal obligations necessitate a change

    Global Team mandates a change

    Opportunity cost of not making the change will stop business continuity operations


    Thanks


    Dan

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Dan,


    Is the column that contains the regulatory, legal, global, etc. a multi-select dropdown or a single-select dropdown?


    Thanks,

    Heather

  • Dan Harris
    Dan Harris ✭✭✭✭✭

    Single select dropdown


    Thanks

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Dan,


    That makes things pretty easy, then. You'll want to be sure the items I have in bold are an exact match to the options for dropdowns. Since I don't know the column name for your dropdown, I've named it drop down. You'll clearly want to change it to your actual column name.

    =if(OR([drop down]@row = "Regulatory", [drop down]@row = "Legal", [drop down]@row = "Global Team"), "Mandatory", "")

    This translates to: If Regulatory, Legal, OR Global Team were selected in the drop down, show "mandatory;" otherwise, leave cell blank.



    Best,

    Heather

  • Dan Harris
    Dan Harris ✭✭✭✭✭

    Thanks, I want to add similar arguments to the formula, how would that work?


    Many Thanks in advance.


    Dan

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Dan,

    If you're wanting to add more options from the drop down to make it display mandatory, you'll simply add

    ,[drop down]@row = "Whatever you want it to say"

    within the OR statement. So it would look like this:

    =if(OR([drop down]@row = "Regulatory", [drop down]@row = "Legal", [drop down]@row = "Global Team", [drop down]@row = "another option", [drop down]@row = "and another one"), "Mandatory", "")

    If you want to have it display something else for other conditions, you'll need to add a nested if statement. For example, if you want it to display Mandatory for all of the categories we previously used, AND wanted it to display Optional for two other categories, it would look like this:

    =if(OR([drop down]@row = "Regulatory", [drop down]@row = "Legal", [drop down]@row = "Global Team"), "Mandatory",if(OR([drop down]@row = "Category A", [drop down]@row = "Category B"), "Optional", ""))

    This translates to: If Regulatory, Legal, OR Global Team were selected in the drop down, show "mandatory;" otherwise, If Category A OR Category B were selected in the drop down, show "optional;" otherwise, leave cell blank.


    Best,

    Heather

  • Dan Harris
    Dan Harris ✭✭✭✭✭

    Amazing thanks will try today and feedback


    Thanks again


    Dan

  • Dan Harris
    Dan Harris ✭✭✭✭✭

    What does the "" represent at the end of the formula, should I adjust depending on the number of arguments?


    Thanks

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Great question! The "" at the end represents "otherwise, leave the cell blank." So, if you add more arguments, you'll add them before the

    , ""))

    Also, if you add more IF statements, you'll end up with more close parenthesis. A good rule of thumb (most times) is that at the end of your formula you'll have the same number of end parenthesis as you have IF statements.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!