Hi,
Check if this works for you:
D E F G 2 4 -2 0 Sheet1
Worksheet Formulas
Cell Formula G2 =IF(OR(F2=0,AND(OR(D2=3,D2=4),OR(F2=-2,F2=-1))),0,IF(F2<0,CHOOSE(D2,0.4,0.5,0.6,0.7),F2*CHOOSE(D2,-0.1,-0.1,-0.2,-0.3)))
I'm struggling with a complex IF formula, hoping someone can help.
In D2 I have a category number 1-4.
In F2 I have a number which could be positive or negative.
I need a formula in G2 to work out the following conditions:
IF F2 is 0, G2 = 0
IF D2 = 1 AND F2 is GREATER than zero MULTIPLY F2 by -0.1.
IF D2 = 1 AND F2 is LESS than zero, G2 = 0.4
IF D2 = 2 AND F2 is GREATER than zero MULTIPLY F2 by -0.1.
IF D2 = 2 AND F2 is LESS than zero, G2 = 0.5
IF D2 = 3 AND F2 is GREATER than zero MULTIPLY F2 by -0.2.
IF D2 = 3 AND F2 is LESS than zero, G2 = 0.6
IF D2 = 3 AND F2 = -1, G2 = 0
IF D2 = 4 AND F2 is GREATER than zero MULTIPLY F2 by -0.3.
IF D2 = 4 AND F2 is LESS than zero, G2 = 0.7
IF D2 = 4 AND F2 = -1 OR -2, G2 = 0
I have a formula which takes into all of the above but I am struggling with adding the part where F2 3 or 4 equal -1 or -1 or -2. I am only getting errors however I do it. Would appreciate any support.
=IF(F2=0,0,IF(AND(F2>0,(D2=4)),F2*-0.3,IF(AND(F2>0,(D2=3)),F2*-0.2,IF(AND(F2>0,(D2=2)),F2*-0.1,IF(AND(F2>0,(D2=1)),F2*-0.1,IF(AND(F2<0,(D2=4)),+0.7,IF(AND(F2<0,(D2=3)),+0.6,IF(AND(F2<0,(D2=2)),+0.5,IF(AND(F2<0,(D2=1)),+0.4,)))))))))
Hi,
Check if this works for you:
D E F G 2 4 -2 0 Sheet1
Worksheet Formulas
Cell Formula G2 =IF(OR(F2=0,AND(OR(D2=3,D2=4),OR(F2=-2,F2=-1))),0,IF(F2<0,CHOOSE(D2,0.4,0.5,0.6,0.7),F2*CHOOSE(D2,-0.1,-0.1,-0.2,-0.3)))
Last edited by jorismoerings; Aug 21st, 2019 at 06:37 AM.
--------------------------------------------------------------------------------
If you found my answer helpful, please hit the Like or Thank button.
Please follow the forum Rules and Guidelines and use Code tags around your VBA code.
Remember: test VBA code always on a copy of your file because usually you can't use <undo>
Try this Macro
Code:Option Explicit Sub Complex_Ifs() Dim my_val Select Case [d2].Value Case 0 my_val = 0 '================== Case 1 Select Case [F2] Case Is >= 0: my_val = [F2] * -0.1 Case Else: my_val = 0.4 End Select '================ Case 2 Select Case [F2] Case Is >= 0: my_val = [F2] * -0.1 Case Else: my_val = 0.5 End Select '================ Case 3 Select Case [F2] Case Is >= 0: my_val = [F2] * -0.2 Case Else: my_val = 0.6 End Select ' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' and so On to the last condition........ ' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Case Else: my_val = "Bad Answer" End Select [G2] = my_val End Sub
Last edited by salim hasan; Aug 21st, 2019 at 06:44 AM.
@jorismoerings:
This part doesn't look correct
=IF(OR(F2=0,AND(OR(D2=3,D2=4),OR(F2=-2,F2=-1))),0
OP said
IF D2 = 3 AND F2 = -1, G2 = 0
If D2 =3 and F2 = -2 your formula will return 0, thats not what the OP said
It should be
IF(OR(F2=0,AND(D2=3,F2=-1),AND(D2=4,OR(F2=-1,F2=-2))),0
Last edited by Special-K99; Aug 21st, 2019 at 06:53 AM.
Hey, try this:
@jorismoeringsCode:CHOOSE(D2,CHOOSE(SIGN(F2)+2,0.4,0,F2*-0.1),CHOOSE(SIGN(F2)+2,0.5,0,F2*-0.1),CHOOSE(SIGN(F2)+2,IF(F2=-1,0,0.6),0,F2*-0.2),CHOOSE(SIGN(F2)+2,IF(OR(F2=-1,F2=-2),0,0.7),0,F2*-0.3))
I think your formula breaks at 1 point: D2 = 3 and F2 = -2, G2 should give 0.6 but yours gives 0, apart from that all good!
√-1 2³ ∑ π
…And it was delicious!
@Special-K99 @tyija1995
Noticed it myself as well, but i used the OP's textComparing list of criteria with text leaves an open interpretation but i think we got the OP on track by shortening his string of multiple IF's.struggling with adding the part where F2 3 or 4 equal -1 or -1 or -2
And by doing the change you suggested OP has 2 possible solution for every option of interpreting the list or the text.
--------------------------------------------------------------------------------
If you found my answer helpful, please hit the Like or Thank button.
Please follow the forum Rules and Guidelines and use Code tags around your VBA code.
Remember: test VBA code always on a copy of your file because usually you can't use <undo>
Thanks all for your excellent responses and apologies for the ambiguity on OP. The formula provided by @tyija1995 works perfectly. I now need to explore 'choose' and 'sign' as I've not seen these before!
Thanks
I show you another option:
Code:=IF(OR(F2=0,AND(F2=-1,D2=3),AND(OR(F2=-1,F2=-2),D2=4)),0,IF(F2<0,LOOKUP(D2,{1,2,3,4},{0.4,0.5,0.6,0.7}),F2*-LOOKUP(D2,{1,2,3,4},{0.1,0.1,0.2,0.3})))
Regards Dante Amor
Still shorter formula, although perhaps not as maintainable:
Code:=IF(F2<0,MID("4567",D2,1),-F2*MID("1123",D2,1))/10*NOT(OR(AND(D2>=3,F2=-1),AND(D2=4,F2=-2)))
Cheers,
Eric
When you eliminate the impossible, whatever remains, however improbable, must be the truth.
-Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
-Post a screen shot with the HTML Maker
Glad it works! CHOOSE and SIGN are pretty cool, I managed to integrate the 2 together also as SIGN will return -1, 0 or 1, so augmenting by +2 results in 1,2,3 which is perfect for CHOOSE function, as the CHOOSE function will then return a result based on the index number! Seeing as your first column has values 1 through 4 (integers) this sets up CHOOSE to be ideal. All-in-all it worked out well using a combination of both.
√-1 2³ ∑ π
…And it was delicious!
Like this thread? Share it with others