Text change based on value of number

Kelly Buse

Hi All,

I was hoping to create a formula that changes the text of a cell based on the number in another cell. Here is what I'm trying to make the cell display at each value range:

• Value is under or equal to 20 - Cell to display "N/A"
• Value is between 21 & 30 - Cell to display "PB to Contact"
• Value is between 31 & 40 - Cell to display "KB to Contact"

I can make the first part of the formula work with the following: =IF(COUNTIF([No. of Days]6, "<=20"), "N/A")

But, when adding the second part - =IF(COUNTIF([No. of Days]6, "<=20", "N/A", IF(COUNTIF([No. of Days]6, ">20", "PB to Contactl")). It just doesn't seem to work! I have been getting the 'Incorrect Argument" and "Unparseable" errors when playing around with it.

Thank you ever so much for your help!

Andrée Starå

Hi Kelly,

Try something like this.

=IF(ISBLANK([No. of Days]@row); ""; IF([No. of Days]@row <= 20; "N/A"; IF([No. of Days]@row <= 30; "PB to Contact"; IF([No. of Days]@row <= 40; "KB to Contact"))))

The same version but with the below changes for your and others convenience.

=IF(ISBLANK([No. of Days]@row), "", IF([No. of Days]@row <= 20, "N/A", IF([No. of Days]@row <= 30, "PB to Contact", IF([No. of Days]@row <= 40, "KB to Contact"))))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

Kelly Buse

Realized that I made a mistake in formula (i.e. PB to Contactl) but it still doesn't work!

Andrée Starå

Hi Kelly,

Try something like this.

=IF(ISBLANK([No. of Days]@row); ""; IF([No. of Days]@row <= 20; "N/A"; IF([No. of Days]@row <= 30; "PB to Contact"; IF([No. of Days]@row <= 40; "KB to Contact"))))

The same version but with the below changes for your and others convenience.

=IF(ISBLANK([No. of Days]@row), "", IF([No. of Days]@row <= 20, "N/A", IF([No. of Days]@row <= 30, "PB to Contact", IF([No. of Days]@row <= 40, "KB to Contact"))))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

Kelly Buse

Hi Andrée,

Yes, worked perfectly! Thank you so much for your help on this. Greatly appreciated!

Andrée Starå

Excellent! 

Happy to help!