Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 6
Exclamation How do you make a hole in a line graph?

So, this project I'm working on shows the percentage of reports 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 month wasn't a total failure or totally successful. And it also doesn't make 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 back 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 have "Show Empty Cells As:" set to "Gaps". I'm using Excel 2007)
Attached Images
  
  #2   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by Katie Reynolds View Post
So, this project I'm working on shows the percentage of reports 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 month wasn't a total failure or totally successful. And it also doesn't make 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 back 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 have "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 after automatically. Perhaps see if you can copy the attached graph into your workbook and link it to your data... You never know, Microsoft might stun us all and have something actually work well!!
Attached Files
File Type: zip LineGraphExample.zip (7.2 KB, 147 views)
  #3   Report Post  
Junior Member
 
Posts: 6
Cool

Quote:
Originally Posted by Spencer101 View Post
The attached was created in Excel 2010 and it does what you're after automatically. Perhaps see if you can copy the attached graph into your workbook and link it to your data... You never know, Microsoft might 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 work because I have a formula in the cell. The formula sets the cell value to =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.

Anyway, I think what I'm going to end up doing is putting the data in a bar graph instead of a line graph, so the empty spaces will for sure be empty. (The line is a running average trend line.)

An alternate solution was to make a macro that deleted the contents of any cells without data and connect it to a button on the sheet.

Anyway, thanks for your help!
Attached Images
 

Last edited by Katie Reynolds : May 22nd 12 at 10:38 PM
  #4   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by Katie Reynolds View Post
That's a good thought, but as it turns out I don't think it will work because I have a formula in the cell. The formula sets the cell value to =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.

Anyway, I think what I'm going to end up doing is putting the data in a bar graph instead of a line graph, so the empty spaces will for sure be empty. (The line is a running average trend line.)

An alternate solution was to make a macro that deleted the contents of any cells without data and connect it to a button on the sheet.

Anyway, thanks for your help!
Is there any chance you could post a dummy version of the table you're making the graph from?
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trying to make a line graph that encomposses many worksheets E[_2_] Excel Discussion (Misc queries) 1 July 22nd 09 11:51 PM
Attn: Golfers! Need Help Calculating Hole-by-Hole Net Handicaps Scott Excel Discussion (Misc queries) 6 May 5th 09 02:42 AM
How to convert a dotted line to a solid line in a line graph Sharlz Charts and Charting in Excel 1 January 14th 09 05:51 AM
How do I make a line graph WITHOUT first line starting at zero? NorthStar Charts and Charting in Excel 9 September 7th 07 02:31 PM
can I make the vertical axis the category axis in Excel line graph power1 Charts and Charting in Excel 1 March 20th 06 09:45 PM


All times are GMT +1. The time now is 09:20 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"