Formula Help

R

Randy

Can anyone tell me what might be wrong with this formula? The second half is
not working...or maybe if there is an easier VB way to do this!

=IF(OR(F7=0,AND(E7="vg_2x1_1",E7="vg_2x1_2",E7="vg_2x1_3")),$P$9,IF(OR(F7=0,AND(E7="vg_2x2_1",E7="vg_2x2_2",E7="vg_2x2_3")),$P$13,"nope"))

Any assistance will be greatly appreciated. Thanks in advance for your time...
 
P

Patrick Molloy

one thing

this

AND(E7="vg_2x1_1",E7="vg_2x1_2",E7="vg_2x1_3")

must always be FALSE since you're trying to AND the same cell with different
values. use OR to return a TRUE if E7 is equal to any one of those values
 
P

Patrick Molloy

try this fix

=IF(AND(F7=0,OR(E7="vg_2x1_1",E7="vg_2x1_2",E7="vg_2x1_3")),$P$9,IF(AND(F7=0,OR(E7="vg_2x2_1",E7="vg_2x2_2",E7="vg_2x2_3")),$P$13,"nope"))
 
R

Randy

That worked perfect....now, I have added this to another portion of a formula
and the last part (very last "If") of this again does not seem to work? Could
it be to long?

=IF(AND(F9=0,OR(E9="vg_2x1_1",E9="vg_2x1_2",E9="vg_2x1_3")),$P$9,IF(AND(F9=0,OR(E9="vg_2x2_1",E9="vg_2x2_2",E9="vg_2x2_3")),$P$13,IF(AND(F9=35,D9=2),$K$9,IF(AND(F9=45,D9=2),$K$10,IF(AND(F9=60,D9=2),$K$11,IF(AND(F9=35,D9=3),$K$16,IF(AND(F9=45,D9=3),$K$17,IF(AND(F9=60,D9=3),$K$18,"nope")))))))
 
J

Jacob Skaria

If you are using 2003 you may only use 7 nested IF statements...

If this post helps click Yes
 
P

Patrick Molloy

7 IF's used to be the max, but your formula is pig ugly (apologies to pig
fans everywhere )! lol

You could create a UDF - A User Defined Function, that could be quite elegent.

something akin to this maybe

Option Explicit
Public Function MyUdf(Esource As Range, Fsource As Range)
Dim res As String
If Range("F9") <> 1 Then
MyUdf = "nope"
Exit Function
End If

Select Case Esource.Value
Case "vg_2x1_1", E9 = "vg_2x1_2", E9 = "vg_2x1_3"
res = Range("P13")
Case "vg_2x2_1", E9 = "vg_2x2_2", E9 = "vg_2x2_3"
res = Range("K16")
End Select

MyUdf = res

End Functio
 
R

Randy

That's what I thought....that's a bummer cause I just needed one
more....thanks for the clarification...will have to try another method.
 
R

Randy

Yes...Thanks Patrick...that was going to be my next method...I will play
around with what you provided and see what I can come up with.

Thanks again for your help with this!
 
Top