Results 1 to 8 of 8

Thread: IF Statement - Help

  1. #1
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    303
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default IF Statement - Help

    Hi there,

    I have an IF statement which I'm almost there with, but I can't get the last part to work..

    =IF(F45=0,"",IF(OR(D8="Company"}),"621/783",IF(OR(D8="Partnership"},F45>0),F14,I14),IF(F45>0),"511/001","521/001")))

    So it's the last IF statement which I can't get to work. IF(F45>0),"511/001","521/001"))). So basically, if it's not Company or Partnership and F45>0, return 511/001, F45<0 return 521/001.

    Thank you!

  2. #2
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,704
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Statement - Help

    Hi,

    Your formula looks, syntactically, a little weird.

    Could you share a short example to illustrate what you're trying to do?

    Matty

  3. #3
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    303
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Statement - Help

    If f45 equals 0, return blank.
    If d8 equals Company return, 621/783
    If d8 equals Partnership and F45>0, return F14 but if F45<0 return I14
    If d8 does not equal Company or Partnerhsip and F45 >0, return "511/001" but if F45 <0 return "521/001".

    It's a formula to automate coding

  4. #4
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,704
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Statement - Help

    Sounds like:

    Code:
    =IF(F45=0,"",IF(D8="Company","321/783",IF(AND(D8="Partnership",F45>0),F14,IF(AND(D8="Partnership",F45<0),I14,IF(AND(OR(D8<>{"Company","Partnership"}),F45>0),"511/001","521/001")))))
    Might be possible to shorten it a little, but it should work. I've also made the assumption that "521/001" is the default if none of the proceeding arguments are met. The formula will need tweaking if this is not the case.

    Matty

  5. #5
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    303
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Statement - Help

    Thanks Matty! That seems to have done the trick.
    Appreciate your help!

  6. #6
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    303
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Statement - Help

    Not sure if you're still awake, but any chance you can tell me where I'm going wrong in this formula..

    =IF(F47=0,"",IF(AND(D8={"Company"},F47>0),494,IF(AND(D8={"Company"},F47<0),474),IF(AND(D8<>{"Company"}),F45>0),"465/"&LEFT(D28,3),"495/"&LEFT(D28,3)))

  7. #7
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    303
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Statement - Help

    Ah-ha! I worked it out. Brackets in the wrong place

    IF(F47=0,"",IF(AND(D8={"Company"},F47>0),494,IF(AND(D8={"Company"},F47<0),474,IF(AND(D8<>{"Company"},F45>0),"465/"&LEFT(D28,3),"495/"&LEFT(D28,3)))))

  8. #8
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,704
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Statement - Help

    Quote Originally Posted by tlc53 View Post
    Ah-ha! I worked it out. Brackets in the wrong place

    IF(F47=0,"",IF(AND(D8={"Company"},F47>0),494,IF(AND(D8={"Company"},F47<0),474,IF(AND(D8<>{"Company"},F45>0),"465/"&LEFT(D28,3),"495/"&LEFT(D28,3)))))
    Glad you're sorted.

    Note that the curly brackets {} are superfluous in the above formula - they're only needed when passing an array, i.e. more than one value.

    Matty
    Last edited by Matty; Mar 3rd, 2019 at 01:56 PM.

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
  •