IF Statements when a score / number is between two values
Hi there! I'm working on a dashboard which will show the overall "health" of a program. I'd like the health score I've already devised to display on a sheet as "Red" "Yellow" or "Green" depending on if the score falls in a certain range.
For example, if the score is less than 84.99, display "Red", between 85 and 94.99, display "Yellow", and 95 or above display "Green".
I'm easily able to write the IF statement for if value < 84.99, "Red" and if value > 95, "Green", but I'm struggling with how to devise the logic for the inbetween Yellow range and nest it all together properly.
Any help would be greatly appreciated!
Comments

It's actually pretty easy, and you are almost there. Nest your red and green statements together and simply add "Yellow" in the last part of the 2nd IF.
=IF(Task@row < 84.99, "Red", IF(Task@row > 95, "Green", "Yellow"))
That third portion of an IF statement is where you put what you want the formula to display if the condition is NOT met. So in this case you say
If this, then Red. If that, then Green. If neither is true, Yellow.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

This worked, thank you so much!

Glad to be of assistance!
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

Hi there,
I'm also trying to find a similar logic for using IF but also coupling it with AND statements to find an inbetween value. For example, I'm trying to do the following with the RYGG dots:
if x cell value is 0, gray
if x cell value is between 0.11.49, Red
if x cell value is between 1.52.99, Yellow
if x cell value is 3, green
Can you help me with this?

Give this a shot...
=IF([Column Name]@row = 0, "Gray", IF([Column Name]@row < 1.5, "Red", IF([Column Name]@row < 3, "Yellow", "Green")))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

Thanks! That worked

Happy to help.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

Hey Paul,
I was wondering if you could help me figure out an if statement that will populate truck times.
For example if column13 is between 6:00 AM and 6:59 AM it will show 7am truck

@Desirae Moore Sure thing! Are you able to provide a screenshot that shows how your times are populated?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

@Paul Newcome This is what I was able to create on excel. I'm trying to do something similar in smartsheet.

Ok. So first we need to isolate the hour. It starts one digit after the " " and end one digit before the ":".
=MID(Modified@row, FIND(" ", Modified@row) + 1, FIND(":", Modified@row)  (FIND(" ", Modified@row) + 1))
Next we convert that into a numerical value:
=VALUE(MID(Modified@row, FIND(" ", Modified@row) + 1, FIND(":", Modified@row)  (FIND(" ", Modified@row) + 1)))
Now we add 1 to that number:
=VALUE(MID(Modified@row, FIND(" ", Modified@row) + 1, FIND(":", Modified@row)  (FIND(" ", Modified@row) + 1))) + 1
Then we convert it back into text and add the am/pm along with "truck":
=VALUE(MID(Modified@row, FIND(" ", Modified@row) + 1, FIND(":", Modified@row)  (FIND(" ", Modified@row) + 1))) + 1 + "" + RIGHT(Modified@row, 2) + " truck"
But... If it is an 11am truck and you want it to read 12:00 PM, then we need to use an IF statement where we output the am/pm to say that if the original value is 11, then output PM.
=VALUE(MID(Modified@row, FIND(" ", Modified@row) + 1, FIND(":", Modified@row)  (FIND(" ", Modified@row) + 1))) + 1 + "" + IF(MID(Modified@row, FIND(" ", Modified@row) + 1, FIND(":", Modified@row)  (FIND(" ", Modified@row) + 1)) = "11", "PM", RIGHT(Modified@row, 2)) + " truck"
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

@Paul Newcome OMG thank you! the only issue is that After a certain time during the day the truck times will change. So here's an example of our truck times.

Ah. I didn't realize you were populating a dropdown. What is the latest time for each of the options?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

@Paul Newcome Those are the latest times. I don't have to populate the dropdown as long as I can get those specific Times to be included in the formula if that makes sense instead of the + 1hr.

The +1hr would work up until the 12PM truck. After that the intervals change to every 1.5hrs and then the last one leaves 2hrs.
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.9K Get Help
 379 Global Discussions
 210 Industry Talk
 441 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 300 Events
 33 Webinars
 7.3K Forum Archives