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 bymarking 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 bymarking 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!