Help Evaluation Really Long Formula (Nested Ifs/Vlookups)

[nirvehex]

Hi All,

I know this is a big ask, but I'm really having trouble evaluating a formula to determine where an error is. The formula is returning 4.5 which is the value in AQ10, but it should return a 7.5 which is the value in DashboardH8. Here is the formula:

=IF(IFERROR(IF(OR(F10="Y",AN10<=6),AQ10,IF(AND(O10="N",SEARCH("Comp",D10),LEFT(D10,2)+0 > 24,AQ10 >=Dashboard!$I$8),AQ10,IF(AND(O10="N",SEARCH("Comp",D10),LEFT(D10,2)+0 > 24,VLOOKUP(A10,'WOs (DSP Only)'!A:B,2,FALSE) >=Dashboard!$I$8,VLOOKUP(A10,'WOs (DSP Only)'!A:C,3,FALSE) >=Dashboard!$I$8,VLOOKUP(A10,'WOs (DSP Only)'!A:D,4,FALSE) >=Dashboard!$I$8,VLOOKUP(A10,'WOs (DSP Only)'!A:E,5,FALSE) >=Dashboard!$I$8,VLOOKUP(A10,'WOs (DSP Only)'!A:F,6,FALSE) >=Dashboard!$I$8,(ROUNDDOWN(365/((AN10*AQ10)/VLOOKUP(CONCAT(D10,"-",O10),Calibration!P:T,5,FALSE)),0)) > ROUNDDOWN(AR10,0)),Dashboard!$I$8,IF(AND(O10="N",SEARCH("Comp",D10),LEFT(D10,2)+0 > 24,VLOOKUP(A10,'WOs (DSP Only)'!A:B,2,FALSE) < Dashboard!$H$8,VLOOKUP(A10,'WOs (DSP Only)'!A:C,3,FALSE) < Dashboard!$H$8,VLOOKUP(A10,'WOs (DSP Only)'!A:D,4,FALSE) < Dashboard!$H$8,VLOOKUP(A10,'WOs (DSP Only)'!A:E,5,FALSE) < Dashboard!$H$8,VLOOKUP(A10,'WOs (DSP Only)'!A:F,6,FALSE) < Dashboard!$H$8,(ROUNDDOWN(365/((AN10*AQ10)/VLOOKUP(CONCAT(D10,"-",O10),Calibration!P:S,4,FALSE)),0)) > ROUNDDOWN(AR10,0)),Dashboard!$H$8,IF(AND(VLOOKUP(A10,'WOs (DSP Only)'!A:B,2,FALSE) >=Dashboard!$H$8,VLOOKUP(A10,'WOs (DSP Only)'!A:B,2,FALSE) < Dashboard!$I$8,ROUNDDOWN(365/((AN10*AQ10)/VLOOKUP(A10,'WOs (DSP Only)'!A:B,2,FALSE)),0) > ROUNDDOWN(AR10,0)),VLOOKUP(A10,'WOs (DSP Only)'!A:B,2,FALSE),IF(AND(VLOOKUP(A10,'WOs (DSP Only)'!A:C,3,FALSE) >=Dashboard!$H$8,VLOOKUP(A10,'WOs (DSP Only)'!A:C,3,FALSE) < Dashboard!$I$8,ROUNDDOWN(365/((AN10*AQ10)/VLOOKUP(A10,'WOs (DSP Only)'!A:C,3,FALSE)),0) > ROUNDDOWN(AR10,0)),VLOOKUP(A10,'WOs (DSP Only)'!A:C,3,FALSE),IF(AND(VLOOKUP(A10,'WOs (DSP Only)'!A:D,4,FALSE) >=Dashboard!$H$8,VLOOKUP(A10,'WOs (DSP Only)'!A:D,4,FALSE) < Dashboard!$I$8,ROUNDDOWN(365/((AN10*AQ10)/VLOOKUP(A10,'WOs (DSP Only)'!A:D,4,FALSE)),0) > ROUNDDOWN(AR10,0)),VLOOKUP(A10,'WOs (DSP Only)'!A:D,4,FALSE),IF(AND(VLOOKUP(A10,'WOs (DSP Only)'!A:E,5,FALSE) >=Dashboard!$H$8,VLOOKUP(A10,'WOs (DSP Only)'!A:E,5,FALSE) < Dashboard!$I$8,ROUNDDOWN(365/((AN10*AQ10)/VLOOKUP(A10,'WOs (DSP Only)'!A:E,5,FALSE)),0) > ROUNDDOWN(AR10,0)),VLOOKUP(A10,'WOs (DSP Only)'!A:E,5,FALSE),IF(AND(VLOOKUP(A10,'WOs (DSP Only)'!A:F,6,FALSE) >=Dashboard!$H$8,VLOOKUP(A10,'WOs (DSP Only)'!A:F,6,FALSE) < Dashboard!$I$8,ROUNDDOWN(365/((AN10*AQ10)/VLOOKUP(A10,'WOs (DSP Only)'!A:F,6,FALSE)),0) > ROUNDDOWN(AR10,0)),VLOOKUP(A10,'WOs (DSP Only)'!A:F,6,FALSE)))))))))),"Not Needed")=FALSE,AQ10,IFERROR(IF(OR(F10="Y",AN10 <=6),AQ10,IF(AND(O10="N",SEARCH("Comp",D10),LEFT(D10,2)+0 > 24,AQ10 >=Dashboard!$I$8),AQ10,IF(AND(O10="N",SEARCH("Comp",D10),LEFT(D10,2)+0 > 24,VLOOKUP(A10,'WOs (DSP Only)'!A:B,2,FALSE) >=Dashboard!$I$8,VLOOKUP(A10,'WOs (DSP Only)'!A:C,3,FALSE) >=Dashboard!$I$8,VLOOKUP(A10,'WOs (DSP Only)'!A:D,4,FALSE) >=Dashboard!$I$8,VLOOKUP(A10,'WOs (DSP Only)'!A:E,5,FALSE) >=Dashboard!$I$8,VLOOKUP(A10,'WOs (DSP Only)'!A:F,6,FALSE) >=Dashboard!$I$8,(ROUNDDOWN(365/((AN10*AQ10)/VLOOKUP(CONCAT(D10,"-",O10),Calibration!P:T,5,FALSE)),0)) > ROUNDDOWN(AR10,0)),Dashboard!$I$8,IF(AND(O10="N",SEARCH("Comp",D10),LEFT(D10,2)+0 > 24,VLOOKUP(A10,'WOs (DSP Only)'!A:B,2,FALSE) < Dashboard!$H$8,VLOOKUP(A10,'WOs (DSP Only)'!A:C,3,FALSE) < Dashboard!$H$8,VLOOKUP(A10,'WOs (DSP Only)'!A:D,4,FALSE) < Dashboard!$H$8,VLOOKUP(A10,'WOs (DSP Only)'!A:E,5,FALSE) < Dashboard!$H$8,VLOOKUP(A10,'WOs (DSP Only)'!A:F,6,FALSE) < Dashboard!$H$8,(ROUNDDOWN(365/((AN10*AQ10)/VLOOKUP(CONCAT(D10,"-",O10),Calibration!P:S,4,FALSE)),0)) > ROUNDDOWN(AR10,0)),Dashboard!$H$8,IF(AND(VLOOKUP(A10,'WOs (DSP Only)'!A:B,2,FALSE) >=Dashboard!$H$8,VLOOKUP(A10,'WOs (DSP Only)'!A:B,2,FALSE) < Dashboard!$I$8,ROUNDDOWN(365/((AN10*AQ10)/VLOOKUP(A10,'WOs (DSP Only)'!A:B,2,FALSE)),0) > ROUNDDOWN(AR10,0)),VLOOKUP(A10,'WOs (DSP Only)'!A:B,2,FALSE),IF(AND(VLOOKUP(A10,'WOs (DSP Only)'!A:C,3,FALSE) >=Dashboard!$H$8,VLOOKUP(A10,'WOs (DSP Only)'!A:C,3,FALSE) < Dashboard!$I$8,ROUNDDOWN(365/((AN10*AQ10)/VLOOKUP(A10,'WOs (DSP Only)'!A:C,3,FALSE)),0) > ROUNDDOWN(AR10,0)),VLOOKUP(A10,'WOs (DSP Only)'!A:C,3,FALSE),IF(AND(VLOOKUP(A10,'WOs (DSP Only)'!A:D,4,FALSE) >=Dashboard!$H$8,VLOOKUP(A10,'WOs (DSP Only)'!A:D,4,FALSE) < Dashboard!$I$8,ROUNDDOWN(365/((AN10*AQ10)/VLOOKUP(A10,'WOs (DSP Only)'!A:D,4,FALSE)),0) > ROUNDDOWN(AR10,0)),VLOOKUP(A10,'WOs (DSP Only)'!A:D,4,FALSE),IF(AND(VLOOKUP(A10,'WOs (DSP Only)'!A:E,5,FALSE) >=Dashboard!$H$8,VLOOKUP(A10,'WOs (DSP Only)'!A:E,5,FALSE) < Dashboard!$I$8,ROUNDDOWN(365/((AN10*AQ10)/VLOOKUP(A10,'WOs (DSP Only)'!A:E,5,FALSE)),0) > ROUNDDOWN(AR10,0)),VLOOKUP(A10,'WOs (DSP Only)'!A:E,5,FALSE),IF(AND(VLOOKUP(A10,'WOs (DSP Only)'!A:F,6,FALSE) >=Dashboard!$H$8,VLOOKUP(A10,'WOs (DSP Only)'!A:F,6,FALSE) < Dashboard!$I$8,ROUNDDOWN(365/((AN10*AQ10)/VLOOKUP(A10,'WOs (DSP Only)'!A:F,6,FALSE)),0) > ROUNDDOWN(AR10,0)),VLOOKUP(A10,'WOs (DSP Only)'!A:F,6,FALSE)))))))))),"Not Needed"))<dashboard!$h$8,vlookup(a10,'wos (dsp="" only)'!a:c,3,false)<dashboard!$h$8,vlookup(a10,'wos="" only)'!a:d,4,false)<dashboard!$h$8,vlookup(a10,'wos="" only)'!a:e,5,false)<dashboard!$h$8,vlookup(a10,'wos="" only)'!a:f,6,false)<dashboard!$h$8,(rounddown(365="" ((an10*aq10)="" vlookup(concat(d10,"-",o10),calibration!p:s,4,false)),0))=""><dashboard!$i$8,rounddown(365 ((an10*aq10)="" vlookup(a10,'wos="" (dsp="" only)'!a:b,2,false)),0)=""><dashboard!$i$8,rounddown(365 ((an10*aq10)="" vlookup(a10,'wos="" (dsp="" only)'!a:c,3,false)),0)=""><dashboard!$i$8,rounddown(365 ((an10*aq10)="" vlookup(a10,'wos="" (dsp="" only)'!a:d,4,false)),0)=""><dashboard!$i$8,rounddown(365 ((an10*aq10)="" vlookup(a10,'wos="" (dsp="" only)'!a:e,5,false)),0)=""><dashboard!$i$8,rounddown(365 ((an10*aq10)="" vlookup(a10,'wos="" (dsp="" only)'!a:f,6,false)),0)=""><dashboard!$h$8,vlookup(a10,'wos (dsp="" only)'!a:c,3,false)<dashboard!$h$8,vlookup(a10,'wos="" only)'!a:d,4,false)<dashboard!$h$8,vlookup(a10,'wos="" only)'!a:e,5,false)<dashboard!$h$8,vlookup(a10,'wos="" only)'!a:f,6,false)<dashboard!$h$8,(rounddown(365="" ((an10*aq10)="" vlookup(concat(d10,"-",o10),calibration!p:s,4,false)),0))=""><dashboard!$i$8,rounddown(365 ((an10*aq10)="" vlookup(a10,'wos="" (dsp="" only)'!a:b,2,false)),0)=""><dashboard!$i$8,rounddown(365 ((an10*aq10)="" vlookup(a10,'wos="" (dsp="" only)'!a:c,3,false)),0)=""><dashboard!$i$8,rounddown(365 ((an10*aq10)="" vlookup(a10,'wos="" (dsp="" only)'!a:d,4,false)),0)=""><dashboard!$i$8,rounddown(365 ((an10*aq10)="" vlookup(a10,'wos="" (dsp="" only)'!a:e,5,false)),0)=""><dashboard!$i$8,rounddown(365 ((an10*aq10)="" vlookup(a10,'wos="" (dsp="" only)'!a:f,6,false)),0)="">

Here are the variables:
F10 = N
AN10 = 22
O10 = N
D10 = 40 Competitor
AQ10 = 4.50
DashboardI8 = 7.5
A10 = ABC123456-9876543-40 Competitor-X
AR10 = 16
DashboardH8 = 5

Formulas:
VLOOKUP(A10,'WOs (DSP Only)'!A:B,2,FALSE) = 3.45
VLOOKUP(A10,'WOs (DSP Only)'!A:C,3,FALSE) = 4.4625
VLOOKUP(A10,'WOs (DSP Only)'!A:D,4,FALSE) = 4.4625
VLOOKUP(A10,'WOs (DSP Only)'!A:E,5,FALSE) = 10.437
VLOOKUP(A10,'WOs (DSP Only)'!A:F,6,FALSE) = 14.7124


VLOOKUP(Calibration!P:T,5,FALSE) = 7.5
VLOOKUP(Calibration!P:S,4,FALSE) = 5

I feel like the formula is just not getting to the point of returning 7.5 or the IF statement's are rearranged wrong. Any help is so very much appreciated.

Thanks!</dashboard!$i$8,rounddown(365></dashboard!$i$8,rounddown(365></dashboard!$i$8,rounddown(365></dashboard!$i$8,rounddown(365></dashboard!$i$8,rounddown(365></dashboard!$h$8,vlookup(a10,'wos></dashboard!$i$8,rounddown(365></dashboard!$i$8,rounddown(365></dashboard!$i$8,rounddown(365></dashboard!$i$8,rounddown(365></dashboard!$i$8,rounddown(365></dashboard!$h$8,vlookup(a10,'wos>

 

[Fluff]

I've no idea where it's going wrong, or why, but have you tried stepping through the formula using the "Evaluate formula" function on the formulas tab?

 

[nirvehex]

Yes, unfortunately I just can't seem to follow. It may just be to complex...

 

[Fluff]

In that case I'm afraid I can't help. I simply don't understand what the formula is doing.

 

[steve the fish]

All your IF(AND formulas in the first part evaluate to FALSE so as they are nested continue to the last IF(AND which has no false argument so evaluates to FALSE. You formula says IF(FALSE=FALSE,AQ10 so hence why you get 4.5. I obviously cant help with your IF(AND formulas and why you think something should evaluate one way or another.

 

[nirvehex]

Thanks guys, that is helpful. How can I help you better understand what I'm trying to do? What else would you need to know?