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

[jgold20]

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<8 00,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<8 00,1.3,IF($J$3<1000,1.2,1.15)))))))))))))

[jgold20]

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(A ND($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]

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

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]

Thank you. I will play around with the VBA code