Forum Discussion

ReneJamis1's avatar
ReneJamis1
Qrew Member
25 days ago

Formula Numeric field not calculating correctly

Hello all,
I have what's sure to be a simple oversight on my part.
I've likely been staring at this to long.
In the following formula, the 1st 3 cases calculate correctly, but the last one does not.
I know the why, but can't seem to figure out the fix.
I have tried putting the closing ) in several places before and after the last /100 but can't seem to close off the Sum() function in the last case statement.

Apologies for the inconvenience on this simple fix.
Here is the formula.
Case([Audit Status],"Abandoned",null,
   Case([Audit Type],"Inbound",
   If([Auto-Fail]=true,0,
   Max(0,Sum(
   Case(Left([IB-Field 1],1),"M",3,"P",2,"D",0,"N",3,3),
   Case(Left([IB-Field 2],1),"M",3,"P",2,"D",0,"N",3,3),
   Case(Left([IB-Field 3],1),"M",7,"P",5,"D",0,"N",7,7),
   Case(Left([IB-Field 4],1),"M",10,"P",6,"D",0,"N",10,10),
   Case(Left([IB-Field 5],1),"M",21,"P",10,"D",0,"N",21,21),
   Case(Left([IB-Field 6],1),"M",15,"P",7,"D",0,"N",15,15),
   Case(Left([IB-Field 7],1),"M",10,"P",6,"D",0,"N",10,10),
   Case(Left([IB-Field 8],1),"M",10,"P",6,"D",0,"N",10,10),
   Case(Left([IB-Field 9],1),"M",3,"P",2,"D",0,"N",3,3),
   Case(Left([IB-Field 10],1),"M",3,"P",2,"D",0,"N",3,3),
   Case(Left([IB-Field 11],1),"M",3,"P",2,"D",0,"N",3,3),
   Case(Left([IB-Field 12],1),"M",3,"P",2,"D",0,"N",3,3),
   Case(Left([IB-Field 13],1),"M",3,"P",2,"D",0,"N",3,3),
   Case(Left([IB-Field 14],1),"M",6,"P",4,"D",0,"N",6,6))/100)),
   
   Case([Audit Type],"Outbound",
   If([Auto-Fail]=true,0,
   Max(0,Sum(
   Case([OB-Field 1],"Yes",5,"No",0,"NA",5,5),
   Case([OB-Field 2],"Yes",5,"No",0,"NA",5,5),
   Case(Left([OB-Field 3],1),"M",10,"P",5,"D",0,"N",10,10),
   Case(Left([OB-Field 4],1),"M",10,"P",5,"D",0,"N",10,10),
   Case(Left([OB-Field 5],1),"M",10,"P",5,"D",0,"N",10,10),
   Case(Left([OB-Field 6],1),"M",10,"P",5,"D",0,"N",10,10),
   Case(Left([OB-Field 7],1),"M",10,"P",5,"D",0,"N",10,10),
   Case([OB-Field 8],"Yes",5,"No",0,"NA",5,5),
   Case([OB-Field 9],"Yes",10,"No",0,"NA",10,10),
   Case([OB-Field 10],"Yes",5,"No",0,"NA",5,5),
   Case([OB-Field 11],"Yes",5,"No",0,"NA",5,5),
   Case([OB-Field 12],"Yes",5,"No",0,"NA",5,5),
   Case([OB-Field 13],"Yes",5,"No",0,"NA",5,5))/100)),
  
   Case([Audit Type],"SDA - Case Work",
   If([Auto-Fail]=true,0,
   Max(0,Sum(
   Case([CW-Field 1],"Yes",10,"No",5,"NA",10,"",10,10),
   Case([CW-Field 2],"Yes",10,"No",5,"NA",10,"",10,10),
   Case([CW-Field 3],"Yes",10,"No",0,"NA",10,"",10,10),
   Case([CW-Field 4],"Yes",10,"No",5,"NA",10,"",10,10),
   Case([CW-Field 5],"Yes",10,"No",0,"NA",10,"",10,10),
   Case([CW-Field 6],"Yes",10,"No",0,"NA",10,"",10,10),
   Case([CW-Field 7],"Yes",10,"No",0,"NA",10,"",10,10),
   Case([CW-Field 8],"Yes",10,"No",0,"NA",10,"",10,10),
   Case([CW-Field 9],"Yes",10,"No",0,"NA",10,"",10,10))/100)),
   
   Case([Audit Type],"OST - Case Work",
   If([Auto-Fail]=true,0,
   Max(0,Sum(
   Case([OST-Assignment],"No",0,"NA",5,5),
   Case(Left([OST-Field 1],1),"M",10,"P",5,"D",0,"N",10,10),
   Case([OST-Case Field 2],"No",0,"NA",10,10),
   Case(Left([OST-Field 3],1),"M",10,"P",5,"D",0,"N",10,10)),
   Case([OST-Field 4],"No",0,"NA",5,5),
   Case(Left([OST-Field 5],1),"M",15,"P",7.5,"D",0,"N",15,15),
   Case(Left([OST-Field 6],1),"M",10,"P",5,"D",0,"N",10,10),
   Case([OST-Field 7],"No",0,"NA",5,5),
   Case([OST-Field 8],"No",0,"NA",5,5),
   Case(Left([OST-Field 9],1),"M",10,"P",5,"D",0,"N",10,10),
   Case(Left([OST-Field 10],1),"M",10,"P",5,"D",0,"N",10,10))/100))))))

Thank you for any help you can provide.

No RepliesBe the first to reply