Results 1 to 10 of 10

Thread: Complex IF formula

  1. #1
    New Member
    Join Date
    May 2015
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Complex IF formula

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

  2. #2
    Board Regular
    Join Date
    Jul 2014
    Location
    The Netherlands
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Complex IF formula

    Hi,

    Check if this works for you:
    DEFG
    24-20

    Sheet1



    Worksheet Formulas
    CellFormula
    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>

  3. #3
    Board Regular
    Join Date
    Dec 2013
    Posts
    102
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complex IF formula

    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.

  4. #4
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,254
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Complex IF formula

    @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.

  5. #5
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Complex IF formula

    Hey, try this:

    Code:
    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))
    @jorismoerings

    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!

  6. #6
    Board Regular
    Join Date
    Jul 2014
    Location
    The Netherlands
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Complex IF formula

    @Special-K99 @tyija1995

    Noticed it myself as well, but i used the OP's text
    struggling with adding the part where F2 3 or 4 equal -1 or -1 or -2
    Comparing 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.
    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>

  7. #7
    New Member
    Join Date
    May 2015
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complex IF formula

    Quote Originally Posted by jorismoerings View Post
    @Special-K99 @tyija1995

    Noticed it myself as well, but i used the OP's text
    Comparing 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.
    And by doing the change you suggested OP has 2 possible solution for every option of interpreting the list or the text.

    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

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,888
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Complex IF formula

    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

  9. #9
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,718
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Complex IF formula

    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

  10. #10
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Complex IF formula

    Quote Originally Posted by fazthfc View Post
    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
    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!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •