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

02/23/21
Accepted

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

Popular Tags:

Best Answers

  • Heather DHeather D ✭✭✭✭✭
    Accepted 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 DHeather D ✭✭✭✭✭
    Accepted 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 DHeather D ✭✭✭✭✭
    Accepted 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 DHeather D ✭✭✭✭✭
    Accepted 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

  • 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 DHeather D ✭✭✭✭✭
    Accepted 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

  • 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 DHeather D ✭✭✭✭✭

    Dan,


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


    Thanks,

    Heather

  • Single select dropdown


    Thanks

  • Heather DHeather D ✭✭✭✭✭

    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

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


    Many Thanks in advance.


    Dan

  • Heather DHeather D ✭✭✭✭✭
    Accepted 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

  • Amazing thanks will try today and feedback


    Thanks again


    Dan

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


    Thanks

  • Heather DHeather D ✭✭✭✭✭

    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.

Sign In or Register to comment.