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"))))))))

Find more posts tagged with

Formulas

Accepted answers

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:

All comments

Matt Lynn ACT

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.