# How to add text conditional on numerical ranges in Google Sheets?

I'm using Google Sheets and I want to display certain text based on the numerical value of another cell. I am a teacher trying to create an assignment where students have to find the best zombie apocalypse location, and they have to tally total zombie attacks through the first 4 days.

Based on each city's total attacks through 4 days, I want the next cell in the row to display 1 of 4 "City Statuses."

• 0-19 attacks should say "Low Risk,"
• 20-49 is "Medium Risk," 50-99 "High Risk" and
• 100+ attacks is "Evacuate Immediately".

I'm imagining a function that is basically like

``````if G2 < 20, write text "Low Risk";
if G2 > 19 & G2 < 50, write text "Medium Risk";
if G2 > 49 & G2 <100, write text "High Risk";
if G2 > 99, write text "Evacuate Immediately".
``````

But I'm a newbie to any functions beyond the very basic and don't know how to put together the formula I need to do this.

I've gotten the conditional formatting down, but as far as I can tell that just changes colors. I want to display one of four possible strings of text based on a range of the numerical value of another cell.

### IFS

You could use nested `IF` functions but they are hard to follow visually and your use case lends itself well to using the `IFS` function. `IFS` is designed to evaluate multiple conditions and return the value associated with the first true condition.

I posted the formulas below in a demo Google Sheet here

``````  # Basic Format

=IFS(condition1, value1, [condition2, …], [value2, …])

# where 'x' is a cell reference

=IFS( x<20, "Low Risk",
x<50, "Medium Risk",
x<100, "High Risk",
x>99, "Evacuate Immediately" )
``````

### Impact of Invalid Source Data

The mathematical comparison operators `<` `>` behave unexpectedly when numbers are compared to either text or NULL values.

``````  # where cell at ref 'x' has value "this"
# where cell at ref 'y' has NULL value

=x<2         =y<2
="this"<2    =""<2
=FALSE       =TRUE

=x>2         =y>2
="this">2    ="">2
=TRUE        =FALSE

``````

This can be avoided using only numbers in the data but optionally the formula can test for these values and manage.

``````  # where cell at ref 'x' has value "this"
# where cell at ref 'y' has NULL value

# Test for text value

=ISTEXT(x)       =ISTEXT(y)
=ISTEXT("this")  =ISTEXT("")
=TRUE            =FALSE

# Test for NULL value

=x=""       =y=""
="this"=""  =""=""
=FALSE      =TRUE

=IFS( OR(ISTEXT(x), x=""), "",
x<20, "Low Risk",
x<50, "Medium Risk",
x<100, "High Risk",
x>99, "Evacuate Immediately" )
``````

Negative Values
Also note that the formula will return "Low Risk" for negative values, which presumably are an indication of an error. If different behavior is preferred a `x<0` check can be added at the beginning of the function.

``````=IFS( OR(ISTEXT(x), x="", x<0), "",
x<20, "Low Risk",
x<50, "Medium Risk",
x<100, "High Risk",
x>99, "Evacuate Immediately" )
``````