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

Question

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.

Answer 1

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" )

Demo Sheet
https://docs.google.com/spreadsheets/d/1q2Xy_2FZxIC6ToKPUHJEBhV-llg9KMSBgWNa0QB0m_A#gid=2100307022