# Nested IF , AND formula

Options

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

• ✭✭✭✭✭✭
Options

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

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.

• ✭✭✭✭✭✭
Options

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.

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.

• ✭✭✭✭✭✭
Options

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

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.

• Options

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

• ✭✭✭✭✭✭
Options

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.

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.

• Options

This formula worked for me:

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!