Why is Nested If Statement Giving me a False

[nirvehex]

Hi,

I'm trying to figure out why this nested if is giving me a false:

=IF(OR(F3="Y",AN3<=6),AQ3,IF(AND(O3="N",SEARCH("Open",D3),AQ3>=3),AQ3,IF(AND(SEARCH("Open",D3),O3="N"),IF(OR(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE)>=3,(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE)))>Details!AR3),AND(VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE)>=3,(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE)))>Details!AR3),AND(VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE)>=3,(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE)))>Details!AR3),AND(VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE)>=3,(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE)))>Details!AR3),AND(VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE)>=3,(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE)))>Details!AR3)),3,IF(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE)>=2,VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE)<3,ROUNDDOWN(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE)),0)>ROUNDDOWN(AR3,0)),VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE),IF(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE)>=2,VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE)<3,ROUNDDOWN(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE)),0)>ROUNDDOWN(AR3,0)),VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE),IF(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE)>=2,VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE)<3,ROUNDDOWN(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE)),0)>ROUNDDOWN(AR3,0)),VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE),IF(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE)>=2,VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE)<3,ROUNDDOWN(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE)),0)>ROUNDDOWN(AR3,0)),VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE),IF(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE)>=2,VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE)<3,ROUNDDOWN(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE)),0)>ROUNDDOWN(AR3,0)),VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE),IF(AND(O3="Y",SEARCH("Open",D3),AQ3>=2),AQ3,IF(AND(SEARCH("Open",D3),O3="Y"),IF(OR(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE)>=2,(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE)))>Details!AR3),AND(VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE)>=2,(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE)))>Details!AR3),AND(VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE)>=2,(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE)))>Details!AR3),AND(VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE)>=2,(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE)))>Details!AR3),AND(VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE)>=2,(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE)))>Details!AR3)),2,IF(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE)>=1,VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE)<2,ROUNDDOWN(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE)),0)>ROUNDDOWN(AR3,0)),VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE),IF(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE)>=1,VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE)<2,ROUNDDOWN(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE)),0)>ROUNDDOWN(AR3,0)),VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE),IF(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE)>=1,VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE)<2,ROUNDDOWN(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE)),0)>ROUNDDOWN(AR3,0)),VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE),IF(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE)>=1,VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE)<2,ROUNDDOWN(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE)),0)>ROUNDDOWN(AR3,0)),VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE),IF(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE)>=1,VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE)<2,ROUNDDOWN(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE)),0)>ROUNDDOWN(AR3,0)),VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE))))))))))))))))))

It's supposed to return a 2.

When I break the code into two pieces, it works.

Working Code that returns a 2:

=IF(AND(SEARCH("Open",D3),O3="Y"),IF(OR(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE)>=2,(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE)))>Details!AR3),AND(VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE)>=2,(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE)))>Details!AR3),AND(VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE)>=2,(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE)))>Details!AR3),AND(VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE)>=2,(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE)))>Details!AR3),AND(VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE)>=2,(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE)))>Details!AR3)),2,IF(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE)>=1,VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE)<2,ROUNDDOWN(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE)),0)>ROUNDDOWN(AR3,0)),VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE),IF(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE)>=1,VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE)<2,ROUNDDOWN(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE)),0)>ROUNDDOWN(AR3,0)),VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE),IF(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE)>=1,VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE)<2,ROUNDDOWN(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE)),0)>ROUNDDOWN(AR3,0)),VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE),IF(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE)>=1,VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE)<2,ROUNDDOWN(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE)),0)>ROUNDDOWN(AR3,0)),VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE),IF(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE)>=1,VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE)<2,ROUNDDOWN(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE)),0)>ROUNDDOWN(AR3,0)),VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE))))))))

I'm trying to add the above code to this code and combine:

=IF(OR(F2="Y",AN2<=6),AQ2,IF(AND(O2="N",SEARCH("Open",D2),AQ2>=3),AQ2,IF(AND(O2="Y",SEARCH("Open",D2),AQ2>=2),AQ2,IF(AND(SEARCH("Open",D2),O2="N"),IF(OR(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)>=3,(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)))>Details!AR2),AND(VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)>=3,(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)))>Details!AR2),AND(VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)>=3,(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)))>Details!AR2),AND(VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)>=3,(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)))>Details!AR2),AND(VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)>=3,(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)))>Details!AR2)),3,IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)<3,ROUNDDOWN(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)),0)>ROUNDDOWN(AR2,0)),VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE),IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)<3,ROUNDDOWN(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)),0)>ROUNDDOWN(AR2,0)),VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE),IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)<3,ROUNDDOWN(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)),0)>ROUNDDOWN(AR2,0)),VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE),IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)<3,ROUNDDOWN(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)),0)>ROUNDDOWN(AR2,0)),VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE),IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)<3,ROUNDDOWN(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)),0)>ROUNDDOWN(AR2,0)),VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE),2))))))))))

When I try to combine the two, that's when I get the false. Any thoughts?

Thanks!

 

[Jeffrey Mahoney]

HOLY COW, that is a long formula! Maybe you should break it up a little.

Usually, if you get a FALSE, it's probably because you haven't provided an ELSE statement at the end of an IF statement. The other possibility is that your AND function is giving you a FALSE.

I'll look at it closer

Jeff

 

[Jeffrey Mahoney]

It looks like a large portion of your IF statement don't have an ELSE. So, if the statement isn't TRUE, then what is it supposed to return. Since I don't have your data, it would take me a long while to solve this. Given that there is over 3640 characters in the formula.

I highly suggest that you take all your VLOOKUP statements out of the formula and put them in separate cells and reference them from there. I can't tell how many times they are repeated in the formula. Every time a VLOOKUP is called it takes more time. If you pull those out, then the call doesn't have to get repeated. You could hide those columns or put them out of site. At least in the interim, the formula would be easier to read.

Jeff

 

[nirvehex]

Thanks Jeff. Before I go to break them up, anyway you could help me figure out how to combine the two below if formulas?

1.

=IF(OR(F2="Y",AN2<=6),AQ2,IF(AND(O2="N",SEARCH("Open",D2),AQ2>=3),AQ2,IF(AND(O2="Y",SEARCH("Open",D2),AQ2>=2),AQ2,IF(AND(SEARCH("Open",D2),O2="N"),IF(OR(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)>=3,(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)))>Details!AR2),AND(VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)>=3,(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)))>Details!AR2),AND(VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)>=3,(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)))>Details!AR2),AND(VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)>=3,(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)))>Details!AR2),AND(VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)>=3,(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)))>Details!AR2)),3,IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)<3,ROUNDDOWN(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)),0)>ROUNDDOWN(AR2,0)),VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE),IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)<3,ROUNDDOWN(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)),0)>ROUNDDOWN(AR2,0)),VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE),IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)<3,ROUNDDOWN(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)),0)>ROUNDDOWN(AR2,0)),VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE),IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)<3,ROUNDDOWN(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)),0)>ROUNDDOWN(AR2,0)),VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE),IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)<3,ROUNDDOWN(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)),0)>ROUNDDOWN(AR2,0)),VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)))))))))))

2.

=IF(AND(SEARCH("Open",D3),O3="Y"),IF(OR(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE)>=2,(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE)))>Details!AR3),AND(VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE)>=2,(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE)))>Details!AR3),AND(VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE)>=2,(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE)))>Details!AR3),AND(VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE)>=2,(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE)))>Details!AR3),AND(VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE)>=2,(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE)))>Details!AR3)),2,IF(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE)>=1,VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE)<2,ROUNDDOWN(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE)),0)>ROUNDDOWN(AR3,0)),VLOOKUP(A3,'WOs (DSP Only)'!A:B,2,FALSE),IF(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE)>=1,VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE)<2,ROUNDDOWN(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE)),0)>ROUNDDOWN(AR3,0)),VLOOKUP(A3,'WOs (DSP Only)'!A:C,3,FALSE),IF(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE)>=1,VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE)<2,ROUNDDOWN(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE)),0)>ROUNDDOWN(AR3,0)),VLOOKUP(A3,'WOs (DSP Only)'!A:D,4,FALSE),IF(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE)>=1,VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE)<2,ROUNDDOWN(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE)),0)>ROUNDDOWN(AR3,0)),VLOOKUP(A3,'WOs (DSP Only)'!A:E,5,FALSE),IF(AND(VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE)>=1,VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE)<2,ROUNDDOWN(365/((AN3*AQ3)/VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE)),0)>ROUNDDOWN(AR3,0)),VLOOKUP(A3,'WOs (DSP Only)'!A:F,6,FALSE))))))))

Thank you so much!

 

[nirvehex]

Nevermind I got it!

=IF(AND(O2="Y",SEARCH("Open",D2)),IF(AND(SEARCH("Open",D2),O2="Y"),IF(OR(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)>=2,(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)))>Details!AR2),AND(VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)>=2,(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)))>Details!AR2),AND(VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)>=2,(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)))>Details!AR2),AND(VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)>=2,(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)))>Details!AR2),AND(VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)>=2,(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)))>Details!AR2)),2,IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)>=1,VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)<2,ROUNDDOWN(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)),0)>ROUNDDOWN(AR2,0)),VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE),IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)>=1,VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)<2,ROUNDDOWN(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)),0)>ROUNDDOWN(AR2,0)),VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE),IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)>=1,VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)<2,ROUNDDOWN(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)),0)>ROUNDDOWN(AR2,0)),VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE),IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)>=1,VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)<2,ROUNDDOWN(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)),0)>ROUNDDOWN(AR2,0)),VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE),IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)>=1,VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)<2,ROUNDDOWN(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)),0)>ROUNDDOWN(AR2,0)),VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)))))))),IF(OR(F2="Y",AN2<=6),AQ2,IF(AND(O2="N",SEARCH("Open",D2),AQ2>=3),AQ2,IF(AND(O2="Y",SEARCH("Open",D2),AQ2>=2),AQ2,IF(AND(SEARCH("Open",D2),O2="N"),IF(OR(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)>=3,(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)))>Details!AR2),AND(VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)>=3,(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)))>Details!AR2),AND(VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)>=3,(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)))>Details!AR2),AND(VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)>=3,(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)))>Details!AR2),AND(VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)>=3,(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)))>Details!AR2)),3,IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)<3,ROUNDDOWN(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE)),0)>ROUNDDOWN(AR2,0)),VLOOKUP(A2,'WOs (DSP Only)'!A:B,2,FALSE),IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)<3,ROUNDDOWN(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE)),0)>ROUNDDOWN(AR2,0)),VLOOKUP(A2,'WOs (DSP Only)'!A:C,3,FALSE),IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)<3,ROUNDDOWN(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE)),0)>ROUNDDOWN(AR2,0)),VLOOKUP(A2,'WOs (DSP Only)'!A:D,4,FALSE),IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)<3,ROUNDDOWN(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE)),0)>ROUNDDOWN(AR2,0)),VLOOKUP(A2,'WOs (DSP Only)'!A:E,5,FALSE),IF(AND(VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)>=2,VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)<3,ROUNDDOWN(365/((AN2*AQ2)/VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE)),0)>ROUNDDOWN(AR2,0)),VLOOKUP(A2,'WOs (DSP Only)'!A:F,6,FALSE))))))))))))

Thanks again Jeff!

 

[Jeffrey Mahoney]

Oh good. You're welcome