Formula Help  Not sure what I am missing here
I'm working with a formula that should indicate the stage of an employee's onboarding based on a table. The stages are labeled as "PHASE COMPLETE," "PHASE NOT STARTED," and "PHASE IN PROCESS." However, I'm encountering a few issues:
 When everything is marked as complete, it returns an "invalid formula" error.
 If no phases are in progress, it doesn't show the next phase but instead gives an "invalid formula" error.
 If everything is marked as not started, it returns an "invalid formula" error.
The table has two columns: "metric" and "value." Any assistance would be appreciated!
=IF(OR(Value15 = "PHASE NOT STARTED", Value15 = "PHASE IN PROCESS"), "Before Start Date", IF(AND(Value15 = "PHASE COMPLETE", OR(Value16 = "PHASE IN PROCESS", Value16 = "PHASE NOT STARTED")), "First Day", IF(AND(Value15 = "PHASE COMPLETE", Value16 = "PHASE COMPLETE", OR(Value17 = "PHASE IN PROCESS", Value17 = "PHASE NOT STARTED")), "First Week", IF(AND(Value15 = "PHASE COMPLETE", Value16 = "PHASE COMPLETE", Value17 = "PHASE COMPLETE", OR(Value18 = "PHASE IN PROCESS", Value18 = "PHASE NOT STARTED")), "First Month", IF(AND(Value15 = "PHASE COMPLETE", Value16 = "PHASE COMPLETE", Value17 = "PHASE COMPLETE", Value18 = "PHASE COMPLETE", OR(Value19 = "PHASE IN PROCESS", Value19 = "PHASE NOT STARTED")), "First Three Months", IF(AND(Value15 = "PHASE COMPLETE", Value16 = "PHASE COMPLETE", Value17 = "PHASE COMPLETE", Value18 = "PHASE COMPLETE", Value19 = "PHASE COMPLETE", OR(Value20 = "PHASE IN PROCESS", Value20 = "PHASE NOT STARTED")), "First Six Months", IF(AND(Value15 = "PHASE COMPLETE", Value16 = "PHASE COMPLETE", Value17 = "PHASE COMPLETE", Value18 = "PHASE COMPLETE", Value19 = "PHASE COMPLETE", Value20 = "PHASE COMPLETE", OR(Value21 = "PHASE IN PROCESS", Value21 = "PHASE NOT STARTED")), "First Year", IF(AND(Value15 = "PHASE NOT STARTED", Value16 = "PHASE NOT STARTED", Value17 = "PHASE NOT STARTED", Value18 = "PHASE NOT STARTED", Value19 = "PHASE NOT STARTED"), Value20 = "PHASE NOT STARTED", Value21 = "PHASE NOT STARTED"), "Before Start Date", IF(Value21 = "PHASE COMPLETE", "Employee Onboarding Complete"))))))))
Best Answer

@Matt LynnPCG I was able to figure out my problem and got the formula working. The issue was the last statement is not needed, simply just needed to add the onboarding complete as the false criteria to the statement before. Below is the correct formula:
=IF(OR(Value15 = "PHASE NOT STARTED", Value15 = "PHASE IN PROCESS"), "Before Start Date", IF(AND(Value15 = "PHASE COMPLETE", OR(Value16 = "PHASE IN PROCESS", Value16 = "PHASE NOT STARTED")), "First Day", IF(AND(Value15 = "PHASE COMPLETE", Value16 = "PHASE COMPLETE", OR(Value17 = "PHASE IN PROCESS", Value17 = "PHASE NOT STARTED")), "First Week", IF(AND(Value15 = "PHASE COMPLETE", Value16 = "PHASE COMPLETE", Value17 = "PHASE COMPLETE", OR(Value18 = "PHASE IN PROCESS", Value18 = "PHASE NOT STARTED")), "First Month", IF(AND(Value15 = "PHASE COMPLETE", Value16 = "PHASE COMPLETE", Value17 = "PHASE COMPLETE", Value18 = "PHASE COMPLETE", OR(Value19 = "PHASE IN PROCESS", Value19 = "PHASE NOT STARTED")), "First Three Months", IF(AND(Value15 = "PHASE COMPLETE", Value16 = "PHASE COMPLETE", Value17 = "PHASE COMPLETE", Value18 = "PHASE COMPLETE", Value19 = "PHASE COMPLETE", OR(Value20 = "PHASE IN PROCESS", Value20 = "PHASE NOT STARTED")), "First Six Months", IF(AND(Value15 = "PHASE COMPLETE", Value16 = "PHASE COMPLETE", Value17 = "PHASE COMPLETE", Value18 = "PHASE COMPLETE", Value19 = "PHASE COMPLETE", Value20 = "PHASE COMPLETE", OR(Value21 = "PHASE IN PROCESS", Value21 = "PHASE NOT STARTED")), "First Twelve Months", "Onboarding Complete")))))))
Answers

@swaldon2 I somewhat recreated this and see the issue that you're having, although not the solution yet. I would recommend maybe breaking this down into several smaller formulas to see if you can isolate the issue. I may do this too, but me not knowing the purpose/data like you it may not jump out at me. It may be just that there's conflicting logic built in, or an order of operations situation. If I see more I'll add it, but I would start be dissecting it in smaller chunks and confirm each one before combining it.

@Matt LynnPCG I was able to figure out my problem and got the formula working. The issue was the last statement is not needed, simply just needed to add the onboarding complete as the false criteria to the statement before. Below is the correct formula:
=IF(OR(Value15 = "PHASE NOT STARTED", Value15 = "PHASE IN PROCESS"), "Before Start Date", IF(AND(Value15 = "PHASE COMPLETE", OR(Value16 = "PHASE IN PROCESS", Value16 = "PHASE NOT STARTED")), "First Day", IF(AND(Value15 = "PHASE COMPLETE", Value16 = "PHASE COMPLETE", OR(Value17 = "PHASE IN PROCESS", Value17 = "PHASE NOT STARTED")), "First Week", IF(AND(Value15 = "PHASE COMPLETE", Value16 = "PHASE COMPLETE", Value17 = "PHASE COMPLETE", OR(Value18 = "PHASE IN PROCESS", Value18 = "PHASE NOT STARTED")), "First Month", IF(AND(Value15 = "PHASE COMPLETE", Value16 = "PHASE COMPLETE", Value17 = "PHASE COMPLETE", Value18 = "PHASE COMPLETE", OR(Value19 = "PHASE IN PROCESS", Value19 = "PHASE NOT STARTED")), "First Three Months", IF(AND(Value15 = "PHASE COMPLETE", Value16 = "PHASE COMPLETE", Value17 = "PHASE COMPLETE", Value18 = "PHASE COMPLETE", Value19 = "PHASE COMPLETE", OR(Value20 = "PHASE IN PROCESS", Value20 = "PHASE NOT STARTED")), "First Six Months", IF(AND(Value15 = "PHASE COMPLETE", Value16 = "PHASE COMPLETE", Value17 = "PHASE COMPLETE", Value18 = "PHASE COMPLETE", Value19 = "PHASE COMPLETE", Value20 = "PHASE COMPLETE", OR(Value21 = "PHASE IN PROCESS", Value21 = "PHASE NOT STARTED")), "First Twelve Months", "Onboarding Complete")))))))

@swaldon2 Good, I was just about to mention that the last item seemed out of place. I usually do my rolling ifs worst (or last) to best (or first). My other comments I was going to mention aren't needed now. Glad you solved it.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 349 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 282 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!