Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Get rid of zeros in formula (Excel xp)

    I need to make several charts that i want to link to the source like in the attached file.

    I want to put a formula in the source range so the zeros don't become part of the bar graph. How do i add to my formulas?

    Thank you for the help.

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Get rid of zeros in formula (Excel xp)

    You could use autofilter to do this!
    Insert a new row 1, above the data.
    In K1 add a heading, say Item, and in L1 a heading, say Number.
    Select K1:L13, and choose Data - Filter - Autofilter.
    Click the dropdown arrow in L1 and choose custom.
    Set up the filter to be greater that 0
    Apply the filter.
    Lastly, select the chart, choose Tools - Options.
    Click the chart tab and check the Plot visible cells only.

    This will only plot data greater than 0

    Hope this helps!
    Regards,
    Rudi

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Get rid of zeros in formula (Excel xp)

    I need to use formulas as this is a small part of a bigger project and i need it to "automatically" take out the zeros. Is there a set of formulas i can use to get rid of them? thanks

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get rid of zeros in formula (Excel xp)

    Have you thought about using an advance data filter (data>Filter>advance filter) and set the criteria for values <>0?

    John

  5. #5
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Get rid of zeros in formula (Excel xp)

    Here's my alternative way with some formula revised and highlighted

    Hope that helps

    Bosco

  6. #6
    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: Get rid of zeros in formula (Excel xp)

    If you don't want to use the autofilter route, an automatic way could be done using dynamic range names and OFFSET.

    See if this is what you are after. It defines a range for the X anf y based on the count of the cells in Col L that are >0. The chart plots these dynamic ranges.

    Steve

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Get rid of zeros in formula (Excel xp)

    Yes, this is it! Thank you very much

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Get rid of zeros in formula (Excel xp)

    Is there a way to do this when a graph has 4 series instead of just one?

  9. #9
    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: Get rid of zeros in formula (Excel xp)

    Define (using OFFSET) 5 range names instead of only 2: the Xrange and the 4 YRanges

    Then use the named ranges as the chart source data.

    Steve

  10. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Get rid of zeros in formula (Excel xp)

    ok, got ya

  11. #11
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Get rid of zeros in formula (Excel xp)

    I can't figure out what i'm doing wrong in the attached. I'm trying to make a dynamic range in case some of the rows are zero. Can u please let me know what i'm doing wrong. Thank you.

  12. #12
    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: Get rid of zeros in formula (Excel xp)

    Your "counti" is yielding 0 since none of the values are >0 (most are text, the others = 0).

    Try:
    =COUNTIF(Bar2!$R$3:$R$15,"<>0")

    Steve

Posting Permissions

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