How do you make a hole in a line graph?

[Katie Reynolds]

So, this project I'm working on shows the percentage of report
submitted each month that are successful.
However, for some months, no reports are submitted, so there is no data
It doesn't really make sense to record it as 0% or 100%, since the mont
wasn't a total failure or totally successful. And it also doesn't mak
sense to show it as somewhere in between the months on either side
because that's not correct

Ideally, the line would just stop at the previous month and start bac
up again at the next month

Does anyone know a way to do this

(I have my empty cell set as =NA(). And in the graph settings, I hav
"Show Empty Cells As:" set to "Gaps". I'm using Excel 2007

+-------------------------------------------------------------------
|Filename: whatitlookslike.JPG
|Download: http://www.excelbanter.com/attachment.php?attachmentid=383
|Filename: whatiwant.JPG
|Download: http://www.excelbanter.com/attachment.php?attachmentid=384
+-------------------------------------------------------------------

 

[Spencer101]

So, this project I'm working on shows the percentage of report
submitted each month that are successful.
However, for some months, no reports are submitted, so there is no data
It doesn't really make sense to record it as 0% or 100%, since the mont
wasn't a total failure or totally successful. And it also doesn't mak
sense to show it as somewhere in between the months on either side
because that's not correct.

Ideally, the line would just stop at the previous month and start bac
up again at the next month.

Does anyone know a way to do this?

(I have my empty cell set as =NA(). And in the graph settings, I hav
"Show Empty Cells As:" set to "Gaps". I'm using Excel 2007)

The attached was created in Excel 2010 and it does what you're afte
automatically. Perhaps see if you can copy the attached graph into you
workbook and link it to your data... You never know, Microsoft migh
stun us all and have something actually work well!

+-------------------------------------------------------------------
|Filename: LineGraphExample.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=385
+-------------------------------------------------------------------

 

[Katie Reynolds]

The attached was created in Excel 2010 and it does what you're afte
automatically. Perhaps see if you can copy the attached graph into you
workbook and link it to your data... You never know, Microsoft migh
stun us all and have something actually work well!!

That's a good thought, but as it turns out I don't think it will wor
because I have a formula in the cell. The formula sets the cell value t
=NA() if there is no data available.

So when I opened your file (in Excel 2010), I tried putting =NA() and "
in the cell, and each time, it drew the line instead of leaving a hole
So it goes.

Anyway, I think what I'm going to end up doing is putting the data in
bar graph instead of a line graph, so the empty spaces will for sure b
empty.

Thanks for your help anyway

+-------------------------------------------------------------------
+-------------------------------------------------------------------