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 in-between 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.
-
This worked, thank you so much!
-
Glad to be of assistance!
-
Hi there,
I'm also trying to find a similar logic for using IF but also coupling it with AND statements to find an in-between 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.1-1.49, Red
if x cell value is between 1.5-2.99, Yellow
if x cell value is 3, green
Can you help me with this?
-
Thanks! That worked
-
-
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?
-
@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"
-
@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?
-
@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.
