IF Statements when a score / number is between two values

Matt.Robinson · 10/04/18

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!

Paul Newcome · 10/04/18

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.

Matt.Robinson · 10/05/18

This worked, thank you so much!

Paul Newcome · 10/05/18

Glad to be of assistance!

ELaliberte · 11/28/18

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?

Paul Newcome · 11/28/18

Give this a shot...

=IF([Column Name]@row = 0, "Gray", IF([Column Name]@row < 1.5, "Red", IF([Column Name]@row < 3, "Yellow", "Green")))

ELaliberte · 11/30/18

Thanks! That worked

Paul Newcome · 11/30/18

Happy to help.

Desirae Moore · 05/19/21

https://community.smartsheet.com/discussion/comment/119446#Comment_119446

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

Paul Newcome · 05/19/21

@Desirae Moore Sure thing! Are you able to provide a screenshot that shows how your times are populated?

Desirae Moore · 05/19/21

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

Paul Newcome · 05/19/21

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"

Desirae Moore · 05/19/21

@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.

Paul Newcome · 05/19/21

Ah. I didn't realize you were populating a dropdown. What is the latest time for each of the options?

Desirae Moore · 05/19/21

@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.

Desirae Moore · 05/19/21

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.

IF Statements when a score / number is between two values

Comments

Categories