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
Best Answers

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,
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,
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

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 😊

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

Dan,
Is the column that contains the regulatory, legal, global, etc. a multiselect dropdown or a singleselect dropdown?
Thanks,
Heather

Single select dropdown
Thanks

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

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

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
Categories
Check out the Formula Handbook template!