# Help with IF formula

Options
edited 12/09/19

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.

«1

• ✭✭✭✭✭✭
Options

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

• Options

• ✭✭✭✭✭✭
Options

Glad I could be of service!

• Options

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)

• ✭✭✭✭✭✭
Options

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.

IF

A1 = "ok",

THEN

B1 divided by 1

ELSE

2 times 0

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

ELSE

2

ELSE

0

• Options

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!

• ✭✭✭✭✭✭
Options

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

• edited 10/04/19
Options

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

• Options

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

• Options

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

• ✭✭✭✭✭✭
Options

Awesome. Glad you figured it out!

• Options

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.

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!