Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If statement (97-02)

    Hi,

    Cells B and C may or maynot have values in them. In cell D I have the following formula. I have pasted the formula down 150 rows. In each row of "D" the last value appears. i.e. if the value of "D5" is 50, then from "D6" to "D150" has the value of 50, but I haven't put anything in "B or C6" yet. If B or C doesn't have a value, I would like for "D6" to show nothing.

    I hope this is clear and someone can help me.

    Thanks in advance.

    Roberta

    =D2-B3+C3
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement (97-02)

    =IF(B3*C3=0,"",D2-B3+C3)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: If statement (97-02)

    Think I understand you.

    Before dragging your formula down the column, put dollar signs ($) before the references you don't want to change.

    Set your formatting to show zeros as blanks.

    Cheers,
    Jim

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement (97-02)

    I think that the formula that you want is:

    <pre>=IF(AND(B3=0,C3=0),"",D2-B3+C3)
    </pre>

    Legare Coleman

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement (97-02)

    Must be too close to holiday. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> She wants to show the result if either B or C is not empty.
    Legare Coleman

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement (97-02)

    Thanks for the responses. I will try to clarify it more.

    B=Withdrawls
    C=Deposits
    D=Balance (=D2-B3+C3)

    B has a value, but C doesn't, so how would I write the formula to evaluate if one of the two doesn't have a value and return nothing("")?

    For instance, Monday my balance was 20 (d2)yesterday I made a deposit of 180(c3) making my new balance 200(d3). I didn't have any withdrawls, so B3 is null..no problem, however as of right now, every row between d4..d150 will show the balance of 200, I don't want that, I want d4..d150 to be null and only change when I put a value in either b4 or c4.

    I think this is more clear and I apologize for the confusion.

    I believe the suggested formula is using the "*" as an "and" but should really be an "or". Simply said ...evaluate b and c to determine if a value is there if both are null...then d would be null.

    Thanks for your help
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement (97-02)

    See the formula in my reply.
    Legare Coleman

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement (97-02)

    Thanks to all....Legare your solution worked perfectly.

    Have a nice and safe 4th of July.

    Roberta
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement (97-02)

    I'm pretty sure my formula works just as well as Legare's...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: If statement (97-02)

    For that matter ...

    =IF(B3*C3,D2-B3+C3,"")

    <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement (97-02)

    Jan: Your formula will not display a result if either of the cells is blank, she wants to not display a value if BOTH cells are blank. My formula will display the calculation if either of the cells contains a value, yours will only display the calculation if both contain a value.
    Legare Coleman

  12. #12
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement (97-02)

    =IF(COUNTBLANK(B3:C3),"",SUM(D2,-B3,C3))

    or

    =IF(COUNTBLANK(B3:C3)>1,"",SUM(D2,-B3,C3))

    if that's what is wanted.
    Microsoft MVP - Excel

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: If statement (97-02)

    I might as well have a go. How about

    =IF(B3+C3,D2-B3+C3,"") <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement (97-02)

    That should work as long as one of the cells is not equal to the negative of the other, and that should be a safe assumption the way this is being used.
    Legare Coleman

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: If statement (97-02)

    Legare, in the specific context of the original post, that would be the case when a withdrawal and a deposit are identical, which might happen with certain kinds of transaction, but the effect would not cause the running balance to be in error.

    (I also interpreted the OP question the way Jan Karel did, but in context I was wrong.)
    -John ... I float in liquid gardens
    UTC -7ąDS

Page 1 of 2 12 LastLast

Posting Permissions

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