Formula Help - Not sure what I am missing here

swaldon2

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:

1. When everything is marked as complete, it returns an "invalid formula" error.
2. If no phases are in progress, it doesn't show the next phase but instead gives an "invalid formula" error.
3. 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"))))))))

swaldon2

@Matt Lynn-PCG 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")))))))

Matt Lynn-PCG

@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.

swaldon2

@Matt Lynn-PCG 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")))))))

Matt Lynn-PCG

@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.