Nested IF Function using Harvey Balls

Options
✭✭✭✭
edited 05/10/23

Hi SS Community,

I have the following nested IF column formula . The formula works properly through the "Red" part, but once I attempt to type DECLINED in the "Revised Final Date" column, the formula returns #invalidoperation. What can I change in my formula to show a Gray Harvey ball when I type DECLINED in the "Revised Final Date" column? Picture posted below.

Thank you,

Aleya Krenek

Education Service Center Region 13

Grants and Contracts Project Coordinator

• ✭✭✭✭✭
Options

Your terms were out of order: with nested ifs, it is important to go from most to least restrictive - its hard to see here because you are using dates and text as tests.

Clean version:

=IF([Revised Final Date]@row = "", "Yellow", IF([Revised Final Date]@row = "Declined", "Gray", IF([Revised Final Date]@row >= TODAY(), "Green", "Red")))

Less work:

=IFERROR(IF([Revised Final Date]@row >= TODAY(), "Green", IF(ISBLANK([Revised Final Date]@row), "Yellow", IF([Revised Final Date]@row < TODAY(), "Red"))), "Gray")

• ✭✭✭✭
Options

@Deric --This worked great! Thank you :)

Best,

Aleya Krenek

Education Service Center Region 13

Grants and Contracts Project Coordinator

• ✭✭✭✭✭
Options

Your terms were out of order: with nested ifs, it is important to go from most to least restrictive - its hard to see here because you are using dates and text as tests.

Clean version:

=IF([Revised Final Date]@row = "", "Yellow", IF([Revised Final Date]@row = "Declined", "Gray", IF([Revised Final Date]@row >= TODAY(), "Green", "Red")))

Less work:

=IFERROR(IF([Revised Final Date]@row >= TODAY(), "Green", IF(ISBLANK([Revised Final Date]@row), "Yellow", IF([Revised Final Date]@row < TODAY(), "Red"))), "Gray")

• ✭✭✭✭
Options

@Deric --This worked great! Thank you :)

Best,

Aleya Krenek

Education Service Center Region 13

Grants and Contracts Project Coordinator

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!