Line charts with 0 being displayed.

**V8Maverick** · 03-16-2009, 02:06 PM

I'm creating a tracker for client sales, cumulatively and weekly, so I have the layout with the dates (every friday) whereby I put in the current, year to date total unit sales and from there the table below does =if(j23="","",sum(j23-j22)), hence when no sales figures have been entered then the cell remains blank and when the new cumulative figure has been entered then it automatically puts in the difference between last weeks figure and this weeks.
The problem with this is that when plotting a line chart for the period, any blank cells are shown as 0, rather than just not drawn. I've tried using NA() instead of blank but no joy.

Edited to add: This is Excel 2002.

Edit: Problem solved, see link below.

**teylyn** · 03-16-2009, 05:31 PM

Hi,

try this:

- select the chart (this is important)
- click Tools - Options and select the Charts tab
- in the Active Charts section at the top select "Not plotted (leave gaps)". If this option is grayed out, you have not selected the chart, or you are using a chart type that does not support gaps (some charts don't)
- hit OK

Hope that helps

**V8Maverick** · 03-17-2009, 03:42 AM

Thanks for that Teylyn... but it doen't work

Still shows a line of zeroes.

**Andy Pope** · 03-17-2009, 07:03 AM

The NA() should stop the line from zeroing although it will not leave a gap, regardless of the Plot empty cells setting.

Can you post example workbook