Updating an Existing Formula (Removing Logic)

[bdav1216]

Hello, see the attached formulas (3 that are doing similar calculations). Currently, the formula is set to output a '1' if the OLB_END < WORKDAY is GT 2 days in the past.

I would like to remove that logic and calculate the rest of the formula without that in the code. It is bolded below in each of the formula's.

=IF(AND([@[Setup Description]]="As-Is (No Changes)",[@[Is Automated]]=1,[@[Yearly OSL]]=1),10,(MIN(IFERROR(VLOOKUP([@Group],'Workload Look Up Table'!$Q:$R,2,FALSE),IF(AND(NOT([@[OLB End]]=""),[@[OLB End]] < WORKDAY(TODAY(),-2)),1,IF(NOT(LEFT([@[Promotion Type]],8)="Analysis"),[@[Order Status Adjustment]]*([@[Complexity Adjustment]]*[@[Setup Description Adjustment]]*[@[Platform Adjustment]]*[@[Order Type Adjustment]]),([@[Order Status Adjustment]]*([@[Complexity Adjustment]]*[@[Setup Description Adjustment]]*[@[Platform Adjustment]]*[@[Order Type Adjustment]])+([@[Order Status Adjustment]]*([@[Complexity Adjustment]]*[@[Setup Description Adjustment]]*[@[Platform Adjustment]]*[@[Order Type Adjustment]])*([@[Jobs Per Order:]]-1)*0.1))))),300)))

=IF(AND([@[Setup Description]]="As-Is (No Changes)",[@[Is Automated]]=1,[@[Yearly OSL]]=1),10,(MIN(IFERROR(VLOOKUP([@[Order Number]],'PROD Workload Look Up Table'!R:S,2,FALSE),IF(AND(NOT([@[OLB End]]=""),[@[OLB End]] < WORKDAY(TODAY(),-2)),1,IF(NOT(LEFT([@[Promotion Type]],8)="Analysis"),([@[PROD Order Status Adjustment]]*([@[Platform Adjustment]]*[@[Order Type Adjustment]]*[@[Complexity Adjustment]]*[@[Setup Description Adjustment]])),(([@[PROD Order Status Adjustment]]*([@[Platform Adjustment]]*[@[Order Type Adjustment]]*[@[Complexity Adjustment]]*[@[Setup Description Adjustment]]))+(([@[PROD Order Status Adjustment]]*([@[Platform Adjustment]]*[@[Order Type Adjustment]]*[@[Complexity Adjustment]]*[@[Setup Description Adjustment]]))*([@[Jobs Per Order:]]-1)*0.1))))),300)))

=IF(AND([@[Setup Description]]="As-Is (No Changes)",[@[Is Automated]]=1,[@[Yearly OSL]]=1),10,(MIN(IF([@CSC]<>[@Primary],0,IF(AND(NOT([@[OLB End]]:[@[OLB End]]=""),[@[OLB End]]:[@[OLB End]] < WORKDAY(TODAY(),-2)),1,IF(NOT(LEFT([@[Promotion Type]],8)="Analysis"),([@[PROD Order Status Adjustment]]*([@[Platform Adjustment]]*[@[Order Type Adjustment]]*[@[Complexity Adjustment]]*[@[Setup Description Adjustment]]))+(IF(AND(NOT([@[OLB End]]=""),[@[OLB End]] < WORKDAY(TODAY(),-2)),1,[@[Order Status Adjustment]]*([@[Complexity Adjustment]]*[@[Setup Description Adjustment]]*[@[Order Type Adjustment]]))),(([@[PROD Order Status Adjustment]]*([@[Platform Adjustment]]*[@[Order Type Adjustment]]*[@[Complexity Adjustment]]*[@[Setup Description Adjustment]])))+(([@[PROD Order Status Adjustment]]*([@[Platform Adjustment]]*[@[Order Type Adjustment]]*[@[Complexity Adjustment]]*[@[Setup Description Adjustment]]))*([@[Job Count:]]-1)*0.1)+(IF(AND(NOT([@[OLB End]]=""),[@[OLB End]] < WORKDAY(TODAY(),-2)),1,([@[Order Status Adjustment]]*([@[Complexity Adjustment]]*[@[Setup Description Adjustment]]*[@[Order Type Adjustment]])))+([@[Order Status Adjustment]]*([@[Complexity Adjustment]]*[@[Setup Description Adjustment]]*[@[Order Type Adjustment]]))*([@[Jobs Per Order:]]-1)*0.1)))),300)))


Let me know if you would like to see some examples.

Currently, if the OLB_END date = April 26, 2018, the formula above would result in a value of '1'.

[JeteMc]

I can't be sure that this works without being able to test, and because of the structured references it is hard to test without a sample spreadsheet.
That said removing the bolded IF statement from the first formula would appear to leave (at least from the standpoint of matching open and closed parentheses):

Please Login or Register  to view this content.

Similarly the second:

Please Login or Register  to view this content.

The Third:

Please Login or Register  to view this content.

Let us know if you have any questions.