# IF / AND / OR WORKS ON EXCELL BUT NOT ON SMARTSHEET

✭✭✭
edited 01/30/24

Hello Everyone,

I have a formula I am working with, that is working just fine on excell but not in smartsheet, it shows #UNPARSEABLE, PLEASE HELP !!

HERE IS THE FORMULA :

=IF(AND(ISBLANK(J5),ISBLANK(J4)),J3,IFS(OR(J3="red",J4="red",J5="red"),"red",OR(AND(J3="green",J4="green",OR(J5="green",ISBLANK(J5)))),"green",OR(AND(OR(J3="yellow",J4="yellow",J5="yellow"),J4<>"red",J5<>"red")),"yellow"))

• ✭✭✭✭✭✭

There is not an IFS function in Smartsheet. You will need to re-write the function using nested IF functions to replace this part:

IFS(OR(STATUS7="red",STATUS8="red",STATUS9="red"),"red",OR(AND(STATUS7="green",STATUS8="green",OR(STATUS9="green",ISBLANK(STATUS9)))),"green",OR(AND(OR(STATUS7="yellow",STATUS8="yellow",STATUS9="yellow"),STATUS8<>"red",STATUS9<>"red")),"yellow")

Nested IF means replacing the value if false with a second IF. Taking this:

=IF( logical_expression, value_if_true, value_if_false)

And doing this:

=IF( logical_expression, value_if_true, IF( logical_expression, value_if_true, value_if_false))

The first part of your formula is fine

=IF(AND(ISBLANK(STATUS9), ISBLANK(STATUS8)), STATUS7)

The red part would become

=IF(OR(STATUS7 = "red", STATUS8 = "red", STATUS9 = "red"), "red")

The green part would become

=IF(AND(STATUS7 = "green", STATUS8 = "green", OR(STATUS9 = "green", ISBLANK(STATUS9))), "green")

The yellow part would become

=IF((AND(OR(STATUS7 = "yellow", STATUS8 = "yellow", STATUS9 = "yellow"), STATUS8 <> "red", STATUS9 <> "red")), "yellow")

If you nest them in this order the yellow part is only evaluated if all the other statements are false, the green part is only evaluated if the first part and the red part are false, the red part is only evaluated if the first part is false.

This is how the nest would look

=IF(AND(ISBLANK(STATUS9), ISBLANK(STATUS8)), STATUS7, IF(OR(STATUS7 = "red", STATUS8 = "red", STATUS9 = "red"), "red", IF(AND(STATUS7 = "green", STATUS8 = "green", OR(STATUS9 = "green", ISBLANK(STATUS9))), "green", IF((AND(OR(STATUS7 = "yellow", STATUS8 = "yellow", STATUS9 = "yellow"), STATUS8 <> "red", STATUS9 <> "red")), "yellow"))))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!