Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: IF? (so close yet so far)

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    New England, USA
    Posts
    129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here is the formula my scatterbrained melon came up with:
    =IF(((V17=0)*(N17>=1)),SUM(V17/SUM(N17-P17)),"No P2s")

    It's not working, of course.

    I would like for Y17 to check the value of V17 and N17. *IF* V = 0 AND N17 >=1, then I wish for v17/sum(N17-P17). If V17 = 0 AND N17< 1 then I would like it to return the text of "No P2s".

    :: chomps on pencil ::

    Your guidance is appreciated.
    ~Deirdre

  2. #2
    New Member
    Join Date
    Mar 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here's what you need, although all this will return is either the value 0 (as zero divided by anything is zero) or "No P2s".

    =IF(AND(V17=0,N17>=1),V17/SUM(N17-P17),"No P2s")

    A simpler formula would be:
    =IF(V17=0,0,"No P2s")

    Hope this helps!

  3. #3
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the code below:
    =IF(V17=0,IF(N17>=1,V17/SUM(N17-P17),"No P2s"),"")

    You did not specify what you want if v17 does equal 0. You can put any text inside the "" that you want displayed.

    I hope this helps.
    Kind regards, Al

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,424
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    try

    =IF(AND(V17=0,N17>=1),V17/(N17-P17),"No P2s")

    but this will yield 0 since V17=0

    so
    =IF(AND(V17=0,N17>=1),0,"No P2s")

    Did I read something incorrectly or is
    some information missing?

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,644
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-14 10:54, Deirdre wrote:
    Here is the formula my scatterbrained melon came up with:
    =IF(((V17=0)*(N17>=1)),SUM(V17/SUM(N17-P17)),"No P2s")

    It's not working, of course.

    I would like for Y17 to check the value of V17 and N17. *IF* V = 0 AND N17 >=1, then I wish for v17/sum(N17-P17). If V17 = 0 AND N17< 1 then I would like it to return the text of "No P2s".

    :: chomps on pencil ::

    Your guidance is appreciated.
    ~Deirdre
    Almost there...

    =IF((V17=0)*(N17>=1),V17/(N17-P17),"No P2s")

    This will return "No P2s" when V17 is not zero and N17 is less than 1.

    Aladin

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,424
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    New England, USA
    Posts
    129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you everyone for your inout and time. I tried them all and still not getting to what I want. Let me try to illustrate, I could very well be doing this the difficult way...

    In retrospect, I see I failed to fully explain my dilema. Please accept my apologies.

    N17 = 7
    V17 = 4
    When this occurs, I would like v17/(N17-P17).

    N17 = 7
    V17 = 0
    When this occurs, I would like for it to return "No P2s"

    N17 = 0
    V17 = 0
    When this occurs, I would like for it to return blank or 0 (doesn't matter)

  8. #8
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Are these the only possible values for V17 and N17?

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    New England, USA
    Posts
    129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Are these the only possible values for V17 and N17?
    No

  10. #10
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Then are these the only values you are interested in looking at? what do want when N17 is greater than 7? When N17 is between 7 and 0? When n17 is less than 0? You need to provide more info please if you want a workable solution.

Some videos you may like

User Tag List

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
  •