Charts: How can exclude certain values from a line graph?

  • What I would like to be able to do is to generate a graph listing processing times of various operations. On occassion, certain processes have a time length of 0 (zero). Is there a way to report the values in a line graph, but automatically missing out any values that are <=0?

    I would be happy with some kind of formula, alternately a VB sol'n is also fine.

    Look forwards to your ideas.


  • Hi Damian,

    Try replacing the <=0 values with =NA()
    Does this give you the effect you are after?

    Charts are always difficult to describe and visualize, which is why I'm not sure why you are using a line to illustate a the time of various operations.
    Would not a column or bar be more appropriate?


  • Basically, the data is collated from other files. It is then transferred onto a row in a blank worksheet. I would like to add a button so that when a cell within a specific column is selected and the macrobutton is pushed, a graph will be produced displaying all the data within that particular row, with the exception of not reporting values of 0 (zero)

    Does that make things clearer?


  • Brains a bit stewed at the mo but AFAIK if you hide rows they're not included in a chart so perhaps go along that route (via a filter perhaps)?

    HTH (?)

    &lt;a href="" target="new"&gt;&lt;img src=""&gt;&lt;/a&gt;

  • I have done something like this using hide rows or columns. but that assumes that you can afford to have the whole column or row hidden.

    Filter is another excellent way to achieve this.

  • Hi Damian,

    I'm still a little unsure of how you chart will look.

    For example if the data has a maximum of 4 data points, a chart of a row which contained the data 1 , 1 , 1 , 1 may look something like this

    Apologies for the charts not being up to my usual standard :wink1:


  • The easist way is to use a spare set of columns to report the required row of data.
    Then, as already suggested, some code to hide any of those columns that contain zero or less.

    I have made an example workbook, so just let me know if you want a copy.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!