Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

up vote 0 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
1  
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
1  
"" 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.