If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
ignoring zero values in excel charts
I'm having a similar problem, except that one of my series
should not be graphed for observations that are the same as another series. I put an #N/A in those cells and they are not graphed. The problem is if some values show up, followed by some #N/As, and then some other values show up in a later observation, Excel interpolates between the observations that are not #N/A. Any ideas? Thanks, JR Graphs will ignore #N/A values, so you could change each cell formula to something like =IF(formula=0,#N/A,formula) Jerry Richard wrote: I am trying to create an x/y scatter plot from an excel sheet where the cells in the data range contain formulae which sometimes return a zero value - when this occurs I want the chart to ignore the value as though the cell was empty but I am having no luck in doing this. It would also be nice if the LINEST function could be made to ignore zero values as well. |
#2
|
|||
|
|||
ignoring zero values in excel charts
That is the advertised behavior of #N/A. Excel does not have a true
missing value, so your only option with intermediate missing values is to delete the formulas from those cells. You could automate the process with an event-linked macro. Complaints about the need for a true missing value predate my 6 years in these newsgroups, so I wouldn't hold my breath about MS addressing this shortcoming any time soon. Jerry Whirlwind wrote: I'm having a similar problem, except that one of my series should not be graphed for observations that are the same as another series. I put an #N/A in those cells and they are not graphed. The problem is if some values show up, followed by some #N/As, and then some other values show up in a later observation, Excel interpolates between the observations that are not #N/A. Any ideas? Thanks, JR Graphs will ignore #N/A values, so you could change each cell formula to something like =IF(formula=0,#N/A,formula) Jerry Richard wrote: I am trying to create an x/y scatter plot from an excel sheet where the cells in the data range contain formulae which sometimes return a zero value - when this occurs I want the chart to ignore the value as though the cell was empty but I am having no luck in doing this. It would also be nice if the LINEST function could be made to ignore zero values as well. |
#3
|
|||
|
|||
ignoring zero values in excel charts
Tushar Mehta has a routine that blanks out these N/A values, which
aren't plotted at the ends of a series, but are interpolated over between valid points. I forget the precise name, but it has the words Chart and N/A in it. Look at the list on the left side of his web site (http://tushar-mehta.com). - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Whirlwind wrote: I'm having a similar problem, except that one of my series should not be graphed for observations that are the same as another series. I put an #N/A in those cells and they are not graphed. The problem is if some values show up, followed by some #N/As, and then some other values show up in a later observation, Excel interpolates between the observations that are not #N/A. Any ideas? Thanks, JR Graphs will ignore #N/A values, so you could change each cell formula to something like =IF(formula=0,#N/A,formula) Jerry Richard wrote: I am trying to create an x/y scatter plot from an excel sheet where the cells in the data range contain formulae which sometimes return a zero value - when this occurs I want the chart to ignore the value as though the cell was empty but I am having no luck in doing this. It would also be nice if the LINEST function could be made to ignore zero values as well. |
#4
|
|||
|
|||
ignoring zero values in excel charts
Many thanks for the posts. I've tried Tushar's Add-in,
but it doesn't appear to be working properly. It's a little quirky really...deleting line segments it shouldn't have and resetting formats to Automatic (maybe only when I turn it off). I'll experiment with it some more, but I'm nearly at the point of giving up in favor of illustrating the data in another way. Thanks again, JR -----Original Message----- Tushar Mehta has a routine that blanks out these N/A values, which aren't plotted at the ends of a series, but are interpolated over between valid points. I forget the precise name, but it has the words Chart and N/A in it. Look at the list on the left side of his web site (http://tushar-mehta.com). - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ |
Thread Tools | |
Display Modes | |
|
|