Formula Help - alternative of using nested if in formula due to size limitation

Question

Can anyone kindly help me with with following salesforce formula I have been trying to write.

Formula is getting fairly complex and large due to number of conditions. Basically there are 4 status fields, for budget, schedule, scope and participation. budget and schedule status are already formula fields while scope and participation being picklists. Now there are 2 more fields (picklists) budget status override and schedule status override. If override fields has values they take priority in calculating overall status. I have been trying to write overall status formula but this is just crossing formula compilation limit of 15k bytes.

This is what I have written.

IF(
    OR(
        ISPICKVAL(Project_Schedule_Override__c,"Red"),
        ISPICKVAL(Project_Budget_Override__c,"Red")
    ),
    "Red",
    IF(
        AND(
            ISBLANK(TEXT(Project_Schedule_Override__c)), 
            NOT(ISBLANK(TEXT(Project_Budget_Override__c))),
            OR(
                ISPICKVAL( ProjectScope_Status__c ,"Yellow"),
                ISPICKVAL( Participation_Status__c ,"Yellow"),
                ISPICKVAL( Project_Budget_Override__c,"Yellow"),
                Project_Schedule_Status__c = "Yellow"
            )
        ),
        "Yellow",
        IF(
            AND(
                ISBLANK(TEXT(Project_Budget_Override__c)), 
                NOT(ISBLANK(TEXT(Project_Schedule_Override__c))),
                OR(
                    ISPICKVAL(ProjectScope_Status__c , "Yellow"),
                    ISPICKVAL( Participation_Status__c ,"Yellow"),
                    Project_Budget_Status__c = "Yellow",
                    ISPICKVAL(Project_Schedule_Override__c,"Yellow")
                )
            ),
            "Yellow",
            IF(
                AND(
                    ISBLANK(TEXT(Project_Schedule_Override__c)),
                    ISBLANK(TEXT(Project_Budget_Override__c)),
                    OR(
                        ISPICKVAL( ProjectScope_Status__c ,"Red"),
                        ISPICKVAL( Participation_Status__c ,"Red"),
                        Project_Budget_Status__c = "Red",
                        Project_Schedule_Status__c = "Red"
                    )
                ),
                "Red",
                IF(
                    AND(
                        ISBLANK(TEXT(Project_Schedule_Override__c)),
                        ISBLANK(TEXT(Project_Budget_Override__c)),
                        OR(
                            ISPICKVAL( ProjectScope_Status__c ,"Yellow"),
                            ISPICKVAL( Participation_Status__c ,"Yellow"),
                            Project_Budget_Status__c = "Yellow",
                            Project_Schedule_Status__c = "Yellow"
                        )
                    ),
                    "Yellow",
                    "Green"
                )
            )
        )
    )
)

Answer 1

Often with this sort of situation, the proper course of action is more of a paradigm shift; rather than fixing or improving this formula, it might be more prudent to ask if a Formula Field is the right tool for the job. It could make more sense to instead use a writable field and populate it from your domain layer. Domain layer here meaning your database hooks, which can be:

1. Before Save Flow
2. Apex Trigger (before events)
3. Workflow Field Update
4. Process Builder

Options 3 and 4 are pretty firmly recommended against at this point as those tools are on their way out. Option 2 is likely overkill, as it will give you a slight performance gain, but cost more in potential maintenance. In a complex org, that might be worthwhile, but in most, option 1 would be the best way to go about field updates from a domain layer.

Once you make the above paradigm shift, it allows you to break out the logic into smaller increments, and compile size of a formula will no longer be a limiting constraint.

Answer 2

There is dirty approach; Introduce dummy fields and create formula around the same. For example,

Project_Schedule_Override__c --> Prj_Sch_Ov__c; the field is simple formula field like TEXT(Project_Schedule_Override__c)

On the consolidate formula field use the abstract fields without the the PICKLIST, TEXT functions. This may help.