| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Help
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... -- Randy Street Rancho Cucamonga, CA |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Help
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 "Randy" wrote: 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... -- Randy Street Rancho Cucamonga, CA |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Help
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")) "Randy" wrote: 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... -- Randy Street Rancho Cucamonga, CA |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Help
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"))))))) -- Randy Street Rancho Cucamonga, CA "Patrick Molloy" wrote: 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")) "Randy" wrote: 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... -- Randy Street Rancho Cucamonga, CA |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Help
If you are using 2003 you may only use 7 nested IF statements...
If this post helps click Yes --------------- Jacob Skaria "Randy" wrote: 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"))))))) -- Randy Street Rancho Cucamonga, CA "Patrick Molloy" wrote: 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")) "Randy" wrote: 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... -- Randy Street Rancho Cucamonga, CA |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Help
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 "Randy" wrote: 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"))))))) -- Randy Street Rancho Cucamonga, CA "Patrick Molloy" wrote: 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")) "Randy" wrote: 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... -- Randy Street Rancho Cucamonga, CA |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Help
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. -- Randy Street Rancho Cucamonga, CA "Jacob Skaria" wrote: If you are using 2003 you may only use 7 nested IF statements... If this post helps click Yes --------------- Jacob Skaria "Randy" wrote: 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"))))))) -- Randy Street Rancho Cucamonga, CA "Patrick Molloy" wrote: 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")) "Randy" wrote: 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... -- Randy Street Rancho Cucamonga, CA |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Help
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! -- Randy Street Rancho Cucamonga, CA "Patrick Molloy" wrote: 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 "Randy" wrote: 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"))))))) -- Randy Street Rancho Cucamonga, CA "Patrick Molloy" wrote: 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")) "Randy" wrote: 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... -- Randy Street Rancho Cucamonga, CA |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula | Excel Worksheet Functions | |||
| extract formula result form cell without running formula again | Excel Programming | |||
| Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
| Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
| Commenting custom formula fields/formula on formula editor | Excel Programming | |||
Linear Mode
