Nested IF Function using Harvey Balls

akrenek

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,

Deric

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

akrenek

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

Best,

Deric

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

akrenek

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

Best,