Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Location
    Virginia, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Charting only non-zero values in a range (2000)

    Sorry if this has been addressed elsewhere. I looked but didn't find anything. I have an Excel 2000 sheet that lists about 50 part #'s in Column A. Each month, columb B is updated with the number of defective units that month. I can easily create a chart that shows all 50 Parts on the X-axis (data range A1:A50) and the qty defective on the Y-axis (B1:B50). The chart shows all 50 parts, whether there are defectives or not. I'd like to have the chart only show those parts that had defective units (i.e. chart only those values in B1:B50 that are non-zero). I'm sure there must be some way to do this, but I'm going in circles. Can anyone help?

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Charting only non-zero values in a range (2000)

    Quick and dirty...

    Try putting Autofilter on the source data, then filter for all defect counts greater than 0.

    The chart should the only show the visible rows.

    HIH

  3. #3
    New Lounger
    Join Date
    Nov 2002
    Location
    Virginia, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Charting only non-zero values in a range (2000)

    AUTOFILTERing the data definately gets me closer to my goal. I was also considering using multiple sheets (Sheet1 would have the raw data, Sheet2 would query Sheet1 and pull out all the non-zero entries, then a dynamic chart would be based on Sheet2). But I guess I was hoping there would be a way to filter data right there in the range specification for the data series. Something like =Series(NonZero(Sheet1!A1:A50) (I realized that's bogus code, but it illustrates what I'm after). Or am I going down a dead end road? If all else fails, I'll definately use the AUTOFILTER you suggest. Thanks for the insight!

  4. #4
    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: Charting only non-zero values in a range (2000)

    Autofilter is your best bet

    BUT

    to "query out" the nonzero entries, on another sheet live, you could use LARGE function to grab all and have the "0s" at the bottom, then use dynamic named range as your chart source, keyed on the entries that are not "0".

    Without more details, I can't be more specific. How you do int depends on how the data is set up and how it will change, and how you want it "sorted" at the 2nd sheet.

    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
  •