PDA

View Full Version : IF(AND statement works - changing to an IF( and IF(OR causes a False - also is there



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)))))))))))))

jgold20
09-23-2018, 05:42 AM
Have resolved (see below) my own problem. Again, is there a better way to do this?

=IF(OR($I$3="NO",$J$3=""),"",IF(AND($A$3="NO",$I$3="PART"),"",
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(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="FULL"),IF($J$3<500,0.6,IF($J$3<1000,0.55,0.5)),
IF(OR($A$3={"B","M","Q","VP","W"}),IF(AND($I$3="FULL"),IF($J$3>0,1.26),IF(AND($I$3="PART"),IF($J$3>0,0.63))),
IF(OR($A$3={"D","DT","H","L","MD","N","SR","V","X","Y"}),IF(AND($I$3="FULL"),IF($J$3<500,1.25,IF($J$3<1000,1.1,0.95)),IF(AND($I$3="PART"),IF($J$3<500,0.9,IF($J$3<1000,0.7,0.5)))))))))))))

Paul_Hossler
09-23-2018, 06:44 AM
That's the reason I first starting learning VBA so I could make a user defined function where I wouldn't need to count parens :rofl:

You might consider a User Defined Function, which might be more maintainable, nd maybe putting the values (600, 700, 800, etc.) in a hidden sheet to make updating easier

(My $0.02)




Option Explicit


Function Something(I3 As Variant, A3 As Variant, J3 As Variant) As Variant
If I3 = "NO" Or Len(J3) = 0 Then
Something = vbNullString
Exit Function
End If

Select Case A3
Case "NO"
If I3 = "FULL" Then
If J3 < 600 Then
Something = 1.5
ElseIf J3 < 700 Then
Something = 1.4
ElseIf J3 < 800 Then
Something = 1.3
ElseIf J3 < 1000 Then
Something = 1.2
Else
Something = 1.15
End If

Else


End If


Case "R"

Case "GE"


Case "B"

Case "D"

End Select
End Function

jgold20
09-23-2018, 06:54 AM
Thank you. I will play around with the VBA code