Nested IF Formula

Michelle McN

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

Kelly Moore

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

https://help.smartsheet.com/function/or

Kelly Moore

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

https://help.smartsheet.com/function/or

Michelle McN

Thanks @Kelly Moore this has worked for me.