IF / AND / OR WORKS ON EXCELL BUT NOT ON SMARTSHEET
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"))
Answers

Hi @Dan Benitah
There is not an IFS function in Smartsheet. You will need to rewrite 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
Categories
Check out the Formula Handbook template!