Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
Tracy Oden 41

# Case formula for multiple fields using IF AND

Hi, I have an existing formula that was working but no longer does because it is receiving an error that the forumla is too long. Can someone please help me with this forumal using CASE.

IF(
AND(ISPICKVAL(Percent_Complete__c, "5%-9% Reviewed"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c = 0)), "On Target",
IF(AND(ISPICKVAL(Percent_Complete__c, "10%-19% Initiated"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c = 0)), "On Target",
IF(AND(ISPICKVAL(Percent_Complete__c, "20%-34%: Stage 1"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c = 0)), "On Target",
IF(AND(ISPICKVAL(Percent_Complete__c, "35%-49%: Stage 2"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c = 0)), "On Target",
IF(AND(ISPICKVAL(Percent_Complete__c, "50%-64%: Stage 3"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c = 0)), "On Target",
IF(AND(ISPICKVAL(Percent_Complete__c, "65%-74%: Stage 4"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c = 0)), "On Target",
IF(AND(ISPICKVAL(Percent_Complete__c, "75%-94%: Stage 5"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c = 0)), "On Target",
IF(AND(ISPICKVAL(Percent_Complete__c, "95%-99%: Stage 6"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c = 0)), "On Target",
IF(AND(ISPICKVAL(Percent_Complete__c, "5%-9% Reviewed"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c = 1)), "On Alert",
IF(AND(ISPICKVAL(Percent_Complete__c, "10%-19% Initiated"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c = 1)), "On Alert",
IF(AND(ISPICKVAL(Percent_Complete__c, "20%-34%: Stage 1"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c = 1)), "On Alert",
IF(AND(ISPICKVAL(Percent_Complete__c, "35%-49%: Stage 2"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c = 1)), "On Alert",
IF(AND(ISPICKVAL(Percent_Complete__c, "50%-64%: Stage 3"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c = 1)), "On Alert",
IF(AND(ISPICKVAL(Percent_Complete__c, "65%-74%: Stage 4"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c = 1)), "On Alert",
IF(AND(ISPICKVAL(Percent_Complete__c, "75%-94%: Stage 5"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c = 1)), "On Alert",
IF(AND(ISPICKVAL(Percent_Complete__c, "95%-99%: Stage 6"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c = 1)), "On Alert",
IF(AND(ISPICKVAL(Percent_Complete__c, "5%-9% Reviewed"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c > 1)), "Red Alert",
IF(AND(ISPICKVAL(Percent_Complete__c, "10%-19% Initiated"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c > 1)), "Red Alert",
IF(AND(ISPICKVAL(Percent_Complete__c, "20%-34%: Stage 1"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c > 1)), "Red Alert",
IF(AND(ISPICKVAL(Percent_Complete__c, "35%-49%: Stage 2"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c > 1)), "Red Alert",
IF(AND(ISPICKVAL(Percent_Complete__c, "50%-64%: Stage 3"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c > 1)), "Red Alert",
IF(AND(ISPICKVAL(Percent_Complete__c, "65%-74%: Stage 4"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c > 1)), "Red Alert",
IF(AND(ISPICKVAL(Percent_Complete__c, "75%-94%: Stage 5"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c > 1)), "Red Alert",
IF(AND(ISPICKVAL(Percent_Complete__c, "95%-99%: Stage 6"), ISPICKVAL (Status__c, "In Progress"),(Days_Late__c > 1)), "Red Alert",
IF(AND(ISPICKVAL(Status__c, "Not Started"),(Days_Late__c >= 1)), "Red Alert",

"")))))))))))))))))))))))))

Your help is greatly appreciated. I have to urgently change this formula.
Best Answer chosen by Tracy Oden 41
Nayana K
IF(TEXT(Status__c) = "In Progress" && 1 = CASE(Percent_Complete__c,"5%-9% Reviewed", 1,"10%-19% Initiated",1,"20%-34%: Stage 1", 1,"35%-49%: Stage 2". 1, "50%-64%: Stage 3", 1, "65%-74%: Stage 4",1,"75%-94%: Stage 5",1,"95%-99%: Stage 6",1,0), IF(Days_Late__c =0, "On Target", IF(Days_Late__c = 1."On Alert",IF(Days_Late__c > 1,"Red Alert",""))), IF(TEXT(Status__c)="Not Started" && Days_Late__c >= 1, "Red Alert", ""))