Nested IF/AND or Something else??


Hi all,

I am working on a formula to determine the Duration of a Build Phase so that I can use it to calculate the Status of a build.

There are several criteria required to determine what Schedule to use. Once I have the Schedule I just need to know the Duration for the Phase it’s in. I’ve built nested IF/AND statements in the past, but I think this will be a pain to maintain due to the number of criteria and options. 

I use an intake sheet to capture the data and this is the current order of the criteria:

(Used to determine the Schedule)

1.    Order Type- (New, Used)

2.    Vehicle Type- (10 options)

3.    Cost- (3 ranges)

4.    Commercial use- (yes/no)

(Used to determine the Duration)

5.    schedule- (10 Options)

6.    Build Phase- (4 options)

7.    Duration- (number of days)

I can then use the duration to determine if the build is “on time”, “risk of delay” or “overdue”.

Thank you all for any help.






Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!