jgold20
09-23-2018, 02:17 AM
Based on an alpha value in A3 and a selection from a drop down in I3 (NO, FULL, PART), I want a numeric value placed in K3. I have this working until I change my IF(AND to an IF(OR and IF(and. The first example works and the second one returns a false on all of the red line when FULL/PART is selected. Also is there another way to do this without the IF statements:.
=IF(OR($A$3="",$I$3="NO",$J$3=""),"",
IF(AND($A$3="B",$I$3="FULL"),IF($J$3<>0,1.26,),IF(AND($A$3="B",$I$3="PART"),IF($J$3<>0,0.63),
IF(AND($A$3="R",$I$3="FULL"),IF($J$3<500,1.5,IF($J$3<1000,1.25,1)),IF(AND($A$3="R",$I$3="PART"),IF($J$3<500,1.25,IF($J$3<1000,1,0.75)),
IF(AND($A$3="GE",$I$3="FULL"),IF($J$3<500,0.95,IF($J$3<1000,0.85,0.75)),IF(AND($A$3="GE",$I$3="PART"),IF($J$3<500,0.6,IF($J$3<1000,0.55,0.5)),
IF(AND($A$3="NO",$I$3<>"FULL"),"",IF($J$3<600,1.5,IF($J$3<700,1.4,IF($J$3<800,1.3,IF($J$3<1000,1.2,1.15))))))))))))
=IF(OR($A$3="",$I$3="NO",$J$3=""),"",
IF(OR($A$3={"B","W"}),IF(AND($I$3="FULL"),IF($J$3<>0,1.26),IF(OR($A$3={"B","W"}),IF(AND($I$3="PART"),IF($J$3<>0,0.63)),
IF(AND($A$3="R",$I$3="FULL"),IF($J$3<500,1.5,IF($J$3<1000,1.25,1)),IF(AND($A$3="R",$I$3="PART"),IF($J$3<500,1.25,IF($J$3<1000,1,0.75)),
IF(AND($A$3="GE",$I$3="FULL"),IF($J$3<500,0.95,IF($J$3<1000,0.85,0.75)),IF(AND($A$3="GE",$I$3="PART"),IF($J$3<500,0.6,IF($J$3<1000,0.55,0.5)),
IF(AND($A$3="NO",$I$3<>"FULL"),"",IF($J$3<600,1.5,IF($J$3<700,1.4,IF($J$3<800,1.3,IF($J$3<1000,1.2,1.15)))))))))))))
=IF(OR($A$3="",$I$3="NO",$J$3=""),"",
IF(AND($A$3="B",$I$3="FULL"),IF($J$3<>0,1.26,),IF(AND($A$3="B",$I$3="PART"),IF($J$3<>0,0.63),
IF(AND($A$3="R",$I$3="FULL"),IF($J$3<500,1.5,IF($J$3<1000,1.25,1)),IF(AND($A$3="R",$I$3="PART"),IF($J$3<500,1.25,IF($J$3<1000,1,0.75)),
IF(AND($A$3="GE",$I$3="FULL"),IF($J$3<500,0.95,IF($J$3<1000,0.85,0.75)),IF(AND($A$3="GE",$I$3="PART"),IF($J$3<500,0.6,IF($J$3<1000,0.55,0.5)),
IF(AND($A$3="NO",$I$3<>"FULL"),"",IF($J$3<600,1.5,IF($J$3<700,1.4,IF($J$3<800,1.3,IF($J$3<1000,1.2,1.15))))))))))))
=IF(OR($A$3="",$I$3="NO",$J$3=""),"",
IF(OR($A$3={"B","W"}),IF(AND($I$3="FULL"),IF($J$3<>0,1.26),IF(OR($A$3={"B","W"}),IF(AND($I$3="PART"),IF($J$3<>0,0.63)),
IF(AND($A$3="R",$I$3="FULL"),IF($J$3<500,1.5,IF($J$3<1000,1.25,1)),IF(AND($A$3="R",$I$3="PART"),IF($J$3<500,1.25,IF($J$3<1000,1,0.75)),
IF(AND($A$3="GE",$I$3="FULL"),IF($J$3<500,0.95,IF($J$3<1000,0.85,0.75)),IF(AND($A$3="GE",$I$3="PART"),IF($J$3<500,0.6,IF($J$3<1000,0.55,0.5)),
IF(AND($A$3="NO",$I$3<>"FULL"),"",IF($J$3<600,1.5,IF($J$3<700,1.4,IF($J$3<800,1.3,IF($J$3<1000,1.2,1.15)))))))))))))