Help with IF formula

Hey Smartsheet Community,

I am really not that great with making long formulas, especially with the "IF" formula. Could someone please help me with making a formula for the RYG balls where if the task is less than 50% complete turn red, if the task is between 50%-99% complete, turn yellow and if the task is 100% complete turn green. I appreciate any help you could give me on this.

Find more posts tagged with

Hospitality

Formulas

Project Management

Comments

Mike Wilday

This formula should do the trick for you. Replace % column title with the actual title of your column. It's set up for row 23... so adjust the row number from 23 to whichever row you want to reference.

=IF([% column title]23 < .5 ,"Red", IF([% column title]23 < .99, "Yellow", IF([% column title]23 = 1, "Green")))

Brian_Vorderburg

Very helpful Mike, thank you!!!

Mike Wilday

Glad I could be of service!

Monah Sifaka

Hello,

Can anyone help me with this formula from Excel I need to transpose into Smartsheet?

=IF(A1="ok";(B1/1,2*0,2);0)

Mike Wilday

Sure!

In Excel A and B refer to columns. Smartsheets refers to columns by the column names. If your column names contains spaces or ends in a number, then you would wrap your column name in brackets.

=IF([Name of Column A]1="ok", [Name of column b]1/1...

But the math and construct of that IF statement doesn't make a lot of sense to me.

A1 = "ok",

THEN

B1 divided by 1

ELSE

2 times 0

----- this is where the formula starts to confuse me ----

ELSE

jdwood1105931

I have (after much trial and error and with help from several coworkers) finally gotten a formula to work which returns the following health statuses:

yellow if the status is In Progress and the end date is in the past
red if the status is Not Started and the end date in the past
green if the status is Complete (no date reference)

It works either of the following ways:

=IF(AND(Status272 = "In Progress", TODAY() > [End Date]272), "Yellow", IF(Status272 = "Complete", "Green", IF(AND(Status272 = "Not Started", TODAY() > [End Date]272), "Red")))

=IF(AND(Status272 = "In Progress", TODAY() > [End Date]272), "Yellow", IF(AND(Status272 = "Not Started", TODAY() > [End Date]272), "Red", IF(Status272 = "Complete", "Green")))

I want to add another condition, which is:

yellow if the status is Not Started and the start date is in the past.

This is how it would be expressed in the formula:

IF(AND(Status272 = "Not Started", TODAY() > [Start Date]272), "Yellow",

I've tried a bunch of different ways to add this string, to no avail. I believe I need a nested OR statement in order to do this, but I can't figure out the correct syntax.

Can anyone help? THANK YOU!

Mike Wilday

Try this one. I would also recommend replacing the row number with @row to make this formula more copy and pasteable.

=IF(AND(Status@row = "In Progress", TODAY() > [End Date]@row), "Yellow", IF(AND(Status@row = "Not Started", TODAY() > [Start Date]@row), "Yellow", IF(Status@row = "Complete", "Green", IF(AND(Status@row = "Not Started", TODAY() > [End Date]@row), "Red"))))

jdwood1105931

For some reason the red portion of this won't work in the string. If I take it out and use it by itself it works. I'm going crazy trying to puzzle this out. I reversed the order of the red and green statements and that didn't make any difference. HELP! See screenshot of results compared to expected results. Everything works, except the red...

Screenshot using the above formula.PNG

jdwood1105931

I figured out the @row trick yesterday by reading a bunch of other posts. That is great!

jdwood1105931

I don't understand why this works, but I switched the order so the yellows are last and now it works!

= IF(Status@row = "Complete", "Green", IF(AND(Status@row = "Not Started", TODAY() > [End Date]@row), "Red", IF(AND(Status@row = "In Progress", TODAY() > [End Date]@row), "Yellow", IF(AND(Status@row = "Not Started", TODAY() > [Start Date]@row), "Yellow"))))

Screenshot using the NEW formula.PNG

Mike Wilday

Awesome. Glad you figured it out!

jdwood1105931

Now I want to add the gray ball and use it if the start date field is blank. I tried the formula below and it doesn't work. Any ideas?

=IF(Status@row = "Complete", "Green", IF(AND(Status@row = "Not Started", TODAY() > [End Date]@row), "Red", IF(AND(Status@row = "In Progress", TODAY() > [End Date]@row), "Yellow", IF(AND(Status@row = "Not Started", TODAY() > [Start Date]@row), "Yellow", IF(ISBLANK([Start Date]@row), "Gray")))))

After I started to add this I also worried that some Completed tasks might have had the dates deleted and I don't want those to turn Gray, so I might need to add another caveat to the green portion of the formula. This is getting awfully complicated.

Mike Wilday

What are you getting instead, for rows that should be grey? Have you tried moving the gray IF to the beginning of the function?

jdwood1105931

It was red instead, but now it is gray using this sequence in the formula. But I have confirmed my fears that if a status is Complete and the date is blank it is gray, when I want green. Can I add a bit that means if status is not Complete to the gray portion? I'm sorry I don't have a good enough understanding of all the possible syntax issues to solve this on my own...

=IF(ISBLANK([Start Date]@row), "Gray", IF(Status@row = "Complete", "Green", IF(AND(Status@row = "Not Started", TODAY() > [End Date]@row), "Red", IF(AND(Status@row = "In Progress", TODAY() > [End Date]@row), "Yellow", IF(AND(Status@row = "Not Started", TODAY() > [Start Date]@row), "Yellow")))))

Andrée Starå

Hi,

Try this.

=IF(AND(ISBLANK([Start Date]@row), Status@row <> "Complete"), "Gray", IF(Status@row = "Complete", "Green", IF(AND(Status@row = "Not Started", TODAY() > [End Date]@row), "Red", IF(AND(Status@row = "In Progress", TODAY() > [End Date]@row), "Yellow", IF(AND(Status@row = "Not Started", TODAY() > [Start Date]@row), "Yellow")))))

Did it work?

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Sign in to join the conversation!

Quick Links

Trending discussions

Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Countifs result not matching filter
In my sheet, I have a filter for 2 values (see below images). The result is 294 In my report this formula yields 304. =COUNTIFS({helper-child}, "1", {gapStatus}, <>"Rejected (not a GAP)", {gapStatus}, <>"removed - duplicate", {gapStatus}, <>"removed - not valid") Why are they not matching? What am I missing?
Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…