Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am plotting a line graph which is a time series. After a certain period of time, the time series shows #NUM! How can I get excel to ignore the #NUM! and plot nothing. Currently, it plots all values of #NUM! as zero which makes my graph inaccurate.

share|improve this question
up vote 1 down vote accepted

Consider using =IFERROR(A1,NA()). This will replace all error values with NA(), which Excel will automatically skip giving you a gap.

share|improve this answer
2  
NA() or #N/A does not leave a gap. It prevents drawing a marker, but if the other points are connected by a line, Excel draws a line between the points on either side of the #N/A. – Jon Peltier Nov 27 '12 at 20:48

You can setup a graph to handle missing data, but not to handle faultive data.

http://www.ucmapi.com/chart/017/gap.png

So, to solve this you should protect your time-series with a code like:

B1=IFERROR(A1;"")

Whereby A1 would be your "raw" data and B1 is the cell/column which you will have to use as chart-input.

enter image description here

share|improve this answer
    
how do i get to hidden and empty cell settings? – daysandtimes Sep 27 '12 at 14:49
    
choose 'select data source' or so, after a right-click into your chart, then the button at the lft-bottom – Jook Sep 27 '12 at 14:53
4  
"" is interpreted by Excel as text, and is assigned a value of zero. If you don't want a point at zero, use NA() in the formula instead of "". This puts the ugly #N/A into the cell, but it is not plotted with a point. If the points are connected by lines, a line is drawn connecting the points on either side of the #N/A. – Jon Peltier Nov 27 '12 at 20:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .