Importing excel IF/And statments to Smartsheets

1996mustang

I use the below IF/AND statement in excel. It works fine.

=IF(O4="Close","",IF(D4="","",IF(AND(M4>0,M4<=0.1),"YELLOW",IF(AND(M4<=0,M4>-0.1),"GREEN",IF(M4<-0.1,"PURPLE",IF(M4>0.1,"RED",))))))

When I import it into Smartsheets, the formula is below and doesn't work. I get #UNPARSEABLE in the cell. Does anybody know why the formula is not working?

=IF(Status3="Close","",IF(Budget3="","",IF(AND([Delta Margin]3>0,[Delta Margin]3<=0.1),"YELLOW",IF(AND([Delta Margin]3<=0,[Delta Margin]3>-0.1),"GREEN",IF([Delta Margin]3<-0.1,"PURPLE",IF([Delta Margin]3>0.1,"RED", ))))))

Best Regards,

Smartsheets snipit.PNG

Find more posts tagged with

Manufacturing

Project Management

Comments

L_123

The first thing I see is the end. In smartsheet if there is a comma the program expects a return. Try adding "" after the last comma.

Also quick note for troubleshooting these stacked if statements, try deleting all of the closing parenthesis at the end of the statement then hit enter. Smartsheet will calculate how many it needs, and put them there for you if your formula is correct.

1996mustang

Thanks for the suggestion but it didn't work. Any more thoughts?

Thanks

Paul Newcome

=IF(Status3 = "Close", "", IF(Budget3 = "", "", IF(AND([Delta Margin]3 > 0, [Delta Margin]3 <= 0.1), "YELLOW", IF(AND([Delta Margin]3 <= 0, [Delta Margin]3 > -0.1), "GREEN", IF([Delta Margin]3 < -0.1, "PURPLE", IF([Delta Margin]3 > 0.1, "RED"))))))

I spaced it out so it is easier for me to read, but other than that the only change I made was to remove the last comma and following space, and your closing parenthesis are correct as is the syntax.

Other than that...

Because the parenthesis in your screenshot are colored but the cell references are not, it leads me to believe there may be a typo in the cell references. Double check your column names.

1996mustang

Yep, that worked. Thank you very much for your assistance.

Best Regards,

Sign in to join the conversation!

Quick Links

Trending discussions

Forms
On the App - "In Smartsheet forms, is there a way to capture or upload an image directly into a specific column cell (per row) without using the Attachments field? I need each section of the form to have its own dedicated image, not just general attachments for the row."
Smartsheet Mobilizer Kit2/Best Practices
Hi - I am launching Smartsheet sessions at my organization for the next 3 months. The SS Mobilizer Kit2 Workspace I saved a few years back, the files are not loading. Is there a new resource kit? Also, I am writing up some best practices for our users: Examples: Names, separate first and last name into two columns. Email…
Workspace is still there but all folders are empty
Hey, I had a Project in January where I created multiple sheets, dashboards etc. Now I want to prepare the same project again but all folders are empty. Not a single sheet is left. How is that possible ? I upgraded to a paid account during that time and Iwanted to do that again but smartsheet do not delete everything if…