Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Formula Help (2007)

    Lounger - I have the following formula =IF(N14=1,"test text",IF(LEN(H14)>=1,"","")).

    The first if statement works fine, but I can't seem to get the second if statement to work

    I'm sure that there is a fairly simple solution - any thoughts?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula Help (2007)

    Hi Dean

    The statement may be flawed slightly as you haven't stated what happens if N14 = 1 and H14 has a length greater that 1 but I am sure there could be a reason for that.

    Back to your original question:

    =IF(N14=1,"test text",IF(LEN(H14)>=1,"","")).

    Should read

    =IF(N14=1,"test text",IF(LEN(H14)>=1,"H14 is longer than 1","")).
    Jerry

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Formula Help (2007)

    Jerry - thanks for your help - its very close

    I'm trying to design this to prompt users to enter text into a cell (in this case H14 - thats based on a Yes/No option button - yes =1,No=2) - thats the first if statement, the second part is supposed to hid the prompt text once some text is entered into the H14.

    I hope that makes sense - any other thoughts?

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula Help (2007)

    Hi Dean

    Yes there are options you can go for and it is the mixture of the AND or OR statements, like so

    1) =IF(AND(LEN(H14)>1,N14=1),"Both are true","")

    The use of the AND statement is when both conditions are TRUE

    2) =IF(OR(LEN(H14)>1,N14=1),"one is true","")

    The use of the OR statement is when either are TRUE
    Jerry

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula Help (2007)

    How about?
    =IF(OR(N14<>1,LEN(H14)>=1),"","test text")

    If this is not right what do you want under the 4 conditions:
    1) N14=1 AND LEN(H14)>=1
    2) N14=1 AND LEN(H14)=0
    3) N14<>1 AND LEN(H14)>=1
    4) N14<>1 AND LEN(H14)=0

    Steve

  6. #6
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Formula Help (2007)

    Thanks Jerry - thats great.

    Regards

    Dean

  7. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Formula Help (2007)

    Steve, thanks for your help - I'll have a play

    This is is what is working for me
    =IF(AND(LEN(H16)>0,N16=1),"",IF(N16=2,"","test text"))

    Thanks for all your help

Posting Permissions

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