Nested IF , AND formula

Susan Gyopar

Hi, I've never used a combined IF with an AND function and I have to perform this function on a test (beginner level)

Use a nested-if formula to automate the RYG status balls in the "Status "column, depending on the "% Complete" for each request.

If a request is less than 70% complete, turn the "Status" column into a "Red" status ball

If a request is 70% or above and less than 100% complete, turn the "Status" column into a "Yellow" status ball

If a request is 100% complete, turn the "Status" column into a "Green" status ball

Question:

Do I put this formula in the status column that is already assigned as a symbol column with RYG balls - I think YES

There is a column named % Complete which is filled in with %s

My formula (error message = unparseable)

= IF([%complete]3<70%,”red”, IF(AND([% complete]3>=70%, [% complete]3<100%,)”yellow”,”green”))

What have I done wrong?

Thanks so much,

Susan

Andrée Starå

Hi @Susan Gyopar

There were multiple errors in your formula.

Try something like this.

=IF([% Complete]@row < 0.7, "Red", IF(AND([% Complete]@row >= 0.7, [% Complete]@row < 1), "Yellow", "Green"))

Did that work/help?

I hope that helps!

Have a fantastic week & Happy Holidays!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå

@Susan Gyopar

Excellent!

You're more than welcome!

The @row and @cell is a best practice.

@row can be used when you're referencing the same row and also removes the need to think about row numbers,

It takes fewer resources and also makes it possible to convert the formula to a Column Formula.

Andrée Starå

Hi @Susan Gyopar

There were multiple errors in your formula.

Try something like this.

=IF([% Complete]@row < 0.7, "Red", IF(AND([% Complete]@row >= 0.7, [% Complete]@row < 1), "Yellow", "Green"))

Did that work/help?

I hope that helps!

Have a fantastic week & Happy Holidays!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Susan Gyopar

Thanks Andree, this formula worked! I understand that the % need to be expressed in decimals, noted. The course I took did not show me that I must use @row, instead the specific row was entered. Do I always use @row, or only if I will use this formula in a drag & fill along the full column?

Thanks again for correcting my errors

Susan

Andrée Starå

@Susan Gyopar

Excellent!

You're more than welcome!

The @row and @cell is a best practice.

@row can be used when you're referencing the same row and also removes the need to think about row numbers,

It takes fewer resources and also makes it possible to convert the formula to a Column Formula.

TeamElevation

This formula worked for me:

=IF([% Complete]@row < 0.7, "Red", IF([% Complete]@row < 1, "Yellow", "Green"))