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

Dan Harris

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

Heather Duff

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

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

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

Heather Duff

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

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

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

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

Dan,

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

Thanks,

Heather

Dan Harris

Single select dropdown

Thanks

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

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

Many Thanks in advance.

Dan

Heather Duff

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

Amazing thanks will try today and feedback

Thanks again

Dan

Dan Harris

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

Thanks

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.