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 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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!