Code:
=IF(OR(F6="Y",AN6<=6),AQ6,
IF(AND(O6="N",SEARCH("Comp",D6),LEFT(D6,2)+0>24,AQ6>=7.5),AQ6,
IF(AND(O6="N",SEARCH("Comp",D6),LEFT(D6,2)+0>24),
IF(OR(AND(VLOOKUP(A6,'WOs (DSP Only)'!A:B,2,FALSE)>=7.5,(365/((AN6*AQ6)/VLOOKUP(A6,'WOs (DSP Only)'!A:B,2,FALSE)))>Details!AR6),AND(VLOOKUP(A6,'WOs (DSP Only)'!A:C,3,FALSE)>=7.5,(365/((AN6*AQ6)/VLOOKUP(A6,'WOs (DSP Only)'!A:C,3,FALSE)))>Details!AR6),AND(VLOOKUP(A6,'WOs (DSP Only)'!A:D,4,FALSE)>=7.5,(365/((AN6*AQ6)/VLOOKUP(A6,'WOs (DSP Only)'!A:D,4,FALSE)))>Details!AR6),AND(VLOOKUP(A6,'WOs (DSP Only)'!A:E,5,FALSE)>=7.5,(365/((AN6*AQ6)/VLOOKUP(A6,'WOs (DSP Only)'!A:E,5,FALSE)))>Details!AR6),AND(VLOOKUP(A6,'WOs (DSP Only)'!A:F,6,FALSE)>=7.5,(365/((AN6*AQ6)/VLOOKUP(A6,'WOs (DSP Only)'!A:F,6,FALSE)))>Details!AR6)),7.5,
IF(AND(O6="N",SEARCH("Comp",D6),LEFT(D6,2)+0>24,VLOOKUP(A6,'WOs (DSP Only)'!A:B,2,FALSE)<5,VLOOKUP(A6,'WOs (DSP Only)'!A:C,3,FALSE)<5,VLOOKUP(A6,'WOs (DSP Only)'!A:D,4,FALSE)<5,VLOOKUP(A6,'WOs (DSP Only)'!A:E,5,FALSE)<5,VLOOKUP(A6,'WOs (DSP Only)'!A:F,6,FALSE)<5,(365/((AN6*AQ6)/VLOOKUP(CONCAT(D6,"-",O6),Calibration!P:S,4,FALSE)))>AR6),5,IF(AND(VLOOKUP(A6,'WOs (DSP Only)'!A:B,2,FALSE)>=5,VLOOKUP(A6,'WOs (DSP Only)'!A:B,2,FALSE)<7.5,ROUNDDOWN(365/((AN6*AQ6)/VLOOKUP(A6,'WOs (DSP Only)'!A:B,2,FALSE)),0)>ROUNDDOWN(AR6,0)),VLOOKUP(A6,'WOs (DSP Only)'!A:B,2,FALSE),IF(AND(VLOOKUP(A6,'WOs (DSP Only)'!A:C,3,FALSE)>=5,VLOOKUP(A6,'WOs (DSP Only)'!A:C,3,FALSE)<7.5,ROUNDDOWN(365/((AN6*AQ6)/VLOOKUP(A6,'WOs (DSP Only)'!A:C,3,FALSE)),0)>ROUNDDOWN(AR6,0)),VLOOKUP(A6,'WOs (DSP Only)'!A:C,3,FALSE),IF(AND(VLOOKUP(A6,'WOs (DSP Only)'!A:D,4,FALSE)>=5,VLOOKUP(A6,'WOs (DSP Only)'!A:D,4,FALSE)<7.5,ROUNDDOWN(365/((AN6*AQ6)/VLOOKUP(A6,'WOs (DSP Only)'!A:D,4,FALSE)),0)>ROUNDDOWN(AR6,0)),VLOOKUP(A6,'WOs (DSP Only)'!A:D,4,FALSE),IF(AND(VLOOKUP(A6,'WOs (DSP Only)'!A:E,5,FALSE)>=5,VLOOKUP(A6,'WOs (DSP Only)'!A:E,5,FALSE)<7.5,ROUNDDOWN(365/((AN6*AQ6)/VLOOKUP(A6,'WOs (DSP Only)'!A:E,5,FALSE)),0)>ROUNDDOWN(AR6,0)),VLOOKUP(A6,'WOs (DSP Only)'!A:E,5,FALSE),IF(AND(VLOOKUP(A6,'WOs (DSP Only)'!A:F,6,FALSE)>=5,VLOOKUP(A6,'WOs (DSP Only)'!A:F,6,FALSE)<7.5,ROUNDDOWN(365/((AN6*AQ6)/VLOOKUP(A6,'WOs (DSP Only)'!A:F,6,FALSE)),0)>ROUNDDOWN(AR6,0)),VLOOKUP(A6,'WOs (DSP Only)'!A:F,6,FALSE)))))))))))
I'm trying to push this part to the end, so it get's evaluated last. Can someone help me rearrange and reconstruct please? I feel like I'm going nuts looking at it...
Code:
IF(OR(AND(VLOOKUP(A6,'WOs (DSP Only)'!A:B,2,FALSE)>=7.5,(365/((AN6*AQ6)/VLOOKUP(A6,'WOs (DSP Only)'!A:B,2,FALSE)))>Details!AR6),AND(VLOOKUP(A6,'WOs (DSP Only)'!A:C,3,FALSE)>=7.5,(365/((AN6*AQ6)/VLOOKUP(A6,'WOs (DSP Only)'!A:C,3,FALSE)))>Details!AR6),AND(VLOOKUP(A6,'WOs (DSP Only)'!A:D,4,FALSE)>=7.5,(365/((AN6*AQ6)/VLOOKUP(A6,'WOs (DSP Only)'!A:D,4,FALSE)))>Details!AR6),AND(VLOOKUP(A6,'WOs (DSP Only)'!A:E,5,FALSE)>=7.5,(365/((AN6*AQ6)/VLOOKUP(A6,'WOs (DSP Only)'!A:E,5,FALSE)))>Details!AR6),AND(VLOOKUP(A6,'WOs (DSP Only)'!A:F,6,FALSE)>=7.5,(365/((AN6*AQ6)/VLOOKUP(A6,'WOs (DSP Only)'!A:F,6,FALSE)))>Details!AR6)),7.5,
Thank you all!
Like this thread? Share it with others