Nested IF Formula
Hi Everyone
I need to create a formula for a workflow status that shows a set % for a workflow stage when the Status column is updated. I.e: if the Status Column shows "In Progress" the Status % column automatically shows "20%".
I've tried various nested IF(AND) and IF(OR) formulas but can't get them to work.
This is what I currently have:
IF(Status@row = “Not Started”), “0%”, IF(OR(Status@row = “Draft”, “10%”), (OR(Status@row = “In Progress”), “20%”, IF(OR(Status@row = “Not Started”), “0%”, IF(OR(Status@row = “In Review”), “50%”, IF(OR(Status@row = “Modification Required”), “20%”, IF (OR(Status@row = “Complete”, “80%”, IF(OR(Status@row = “Testing”, “90%”), IF(OR(Status@row = “Published”), “100%”)))))))))
Thanks for your help.
Michelle
Best Answer

Hey @Michelle McN
I corrected the above formula. From what I could tell, you only needed one IF/OR  the response of 20% can be a result of either "In Progress" OR "Modification Required". Also in the formula, all of the IFs were closed off too early  I removed the parentheses so that the IF parentheses don't close until the end of the formula. Your formula also contained slanted quotes, not straight quotes. I changed those. Finally, numbers shouldn't be enclosed in quotes unless you are forcing them to become a text string. Also, percentages are actually decimals, assuming these aren't originating from some external sources.
=IF(Status@row = "Not Started", 0, IF(Status@row = "Draft", 0.1, IF(OR(Status@row = "In Progress", Status@row = "Modification Required"), 0.2, IF(Status@row = "In Review", 0.5, IF(Status@row = "Complete", 0.8, IF(Status@row = "Testing", 0.9, IF(Status@row = "Published", 1)))))))
Does this work for you?
Kelly
Answers

Hey @Michelle McN
I corrected the above formula. From what I could tell, you only needed one IF/OR  the response of 20% can be a result of either "In Progress" OR "Modification Required". Also in the formula, all of the IFs were closed off too early  I removed the parentheses so that the IF parentheses don't close until the end of the formula. Your formula also contained slanted quotes, not straight quotes. I changed those. Finally, numbers shouldn't be enclosed in quotes unless you are forcing them to become a text string. Also, percentages are actually decimals, assuming these aren't originating from some external sources.
=IF(Status@row = "Not Started", 0, IF(Status@row = "Draft", 0.1, IF(OR(Status@row = "In Progress", Status@row = "Modification Required"), 0.2, IF(Status@row = "In Review", 0.5, IF(Status@row = "Complete", 0.8, IF(Status@row = "Testing", 0.9, IF(Status@row = "Published", 1)))))))
Does this work for you?
Kelly

Thanks @Kelly Moore this has worked for me.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!