function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Tracy Oden 41Tracy 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 KNayana 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", ""))

please give this a try