Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    2000 (Totals with out 0.00)

    Hi, I need a formula that says if nothing is in A2,B2,C2 for example. Then don't give me the 0's where my formula is. The formula is from D2850. example A2+B2+C2 = (formula) in D2. the problem is that when I delete the information in A,B,C but keeping the formula, I get all the 0.00 in D. How do I get rid of the 0.00 when there is nothing in A2,B2,and C2.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2000 (Totals with out 0.00)

    Try this:
    =IF(AND(ISBLANK(A2),ISBLANK(B2),ISBLANK(C2)),"",A2 +B2+C2)
    or:
    =IF(AND(ISBLANK(A2),ISBLANK(B2),ISBLANK(C2)),"",SU M(A2:C2))
    or as array formula (confirm with CTRL+SHIFT+ENTER):
    =IF(ISBLANK(A2:C2),"",A2+B2+C2)
    or, also as array formula:
    =IF(ISBLANK(A2:C2),"",SUM(A2:C2))

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2000 (Totals with out 0.00)

    You could also use a custom format
    0,00 ; -0,00 ; ""
    This is belgian notation, replace punctuation by your settings.
    The first argument is for positive numbers (0,00)
    The second argument is for negative numbers (-0,00)
    The third argument is for 0 ("")
    Francois

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2000 (Totals with out 0.00)

    If this suits buckshot's purpose, it is much easier than my solution. But it will also suppress 0 if it is a "valid" result. For example, if A2 = 10, B2 = -7 and C2 = -3, A2+B2+C2 = 0.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2000 (Totals with out 0.00)

    Right, up to him to decide
    Francois

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: 2000 (Totals with out 0.00)

    And yet another:
    =IF(AND(A2=0,B2=0,C2=0),"",A2+B2+C2)
    though, unlike your ISBLANK formulae, also returns a null if A2, B2 and C2 all have 0s in them.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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