If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
breaks in line charts
I have a data table that pulls information from other places in my workbook.
Therefore, cells are not empty. I have a simple line chart and I want to have breaks on the line if there is no data on a particular cell. However, if I don't have data it plots it as zero. Any suggestions? Thanks! |
#2
|
|||
|
|||
I found the solution in K. Georgeatis formula, back in May:
=IF(ISNUMBER(A1),A1,NA()) Thanks for posting it! "Myriam" wrote: I have a data table that pulls information from other places in my workbook. Therefore, cells are not empty. I have a simple line chart and I want to have breaks on the line if there is no data on a particular cell. However, if I don't have data it plots it as zero. Any suggestions? Thanks! |
#3
|
|||
|
|||
NA() will prevent a plot symbol, but will not break the line if there
are actual values on either side of it. If you need that latter behavior, your only option is to delete the formula. You could automate the process of deleting and restoring the formula by tying it to the worksheet change event. Jerry Myriam wrote: I found the solution in K. Georgeatis formula, back in May: =IF(ISNUMBER(A1),A1,NA()) Thanks for posting it! "Myriam" wrote: I have a data table that pulls information from other places in my workbook. Therefore, cells are not empty. I have a simple line chart and I want to have breaks on the line if there is no data on a particular cell. However, if I don't have data it plots it as zero. Any suggestions? Thanks! |
#4
|
|||
|
|||
You're right. It solved my immediate challenge but I was going to get stuck
in the 2nd part. Can you please tell me how to tie it to the worksheet change event.? Thanks! "Jerry W. Lewis" wrote: NA() will prevent a plot symbol, but will not break the line if there are actual values on either side of it. If you need that latter behavior, your only option is to delete the formula. You could automate the process of deleting and restoring the formula by tying it to the worksheet change event. Jerry Myriam wrote: I found the solution in K. Georgeatis formula, back in May: =IF(ISNUMBER(A1),A1,NA()) Thanks for posting it! "Myriam" wrote: I have a data table that pulls information from other places in my workbook. Therefore, cells are not empty. I have a simple line chart and I want to have breaks on the line if there is no data on a particular cell. However, if I don't have data it plots it as zero. Any suggestions? Thanks! |
#5
|
|||
|
|||
Myriam -
Both Andy Pope (http://andypopo.info) and Tushar Mehta (http://tushar-mehta.com) have solutions on their web sites to the lack of a gap in the line where there's an #N/A error value. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Myriam wrote: You're right. It solved my immediate challenge but I was going to get stuck in the 2nd part. Can you please tell me how to tie it to the worksheet change event.? Thanks! "Jerry W. Lewis" wrote: NA() will prevent a plot symbol, but will not break the line if there are actual values on either side of it. If you need that latter behavior, your only option is to delete the formula. You could automate the process of deleting and restoring the formula by tying it to the worksheet change event. Jerry Myriam wrote: I found the solution in K. Georgeatis formula, back in May: =IF(ISNUMBER(A1),A1,NA()) Thanks for posting it! "Myriam" wrote: I have a data table that pulls information from other places in my workbook. Therefore, cells are not empty. I have a simple line chart and I want to have breaks on the line if there is no data on a particular cell. However, if I don't have data it plots it as zero. Any suggestions? Thanks! |
#6
|
|||
|
|||
Jon,
Thanks for the links. The first one, I couldn't open. Tushar-mehta's add-in is fantastic! "Jon Peltier" wrote: Myriam - Both Andy Pope (http://andypopo.info) and Tushar Mehta (http://tushar-mehta.com) have solutions on their web sites to the lack of a gap in the line where there's an #N/A error value. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Myriam wrote: You're right. It solved my immediate challenge but I was going to get stuck in the 2nd part. Can you please tell me how to tie it to the worksheet change event.? Thanks! "Jerry W. Lewis" wrote: NA() will prevent a plot symbol, but will not break the line if there are actual values on either side of it. If you need that latter behavior, your only option is to delete the formula. You could automate the process of deleting and restoring the formula by tying it to the worksheet change event. Jerry Myriam wrote: I found the solution in K. Georgeatis formula, back in May: =IF(ISNUMBER(A1),A1,NA()) Thanks for posting it! "Myriam" wrote: I have a data table that pulls information from other places in my workbook. Therefore, cells are not empty. I have a simple line chart and I want to have breaks on the line if there is no data on a particular cell. However, if I don't have data it plots it as zero. Any suggestions? Thanks! |
#7
|
|||
|
|||
The link to Andy Pope's site is:
http://andypope.info/ It's definitely worth a visit! Myriam wrote: Jon, Thanks for the links. The first one, I couldn't open. Tushar-mehta's add-in is fantastic! "Jon Peltier" wrote: Myriam - Both Andy Pope (http://andypopo.info) and Tushar Mehta (http://tushar-mehta.com) have solutions on their web sites to the lack of a gap in the line where there's an #N/A error value. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Myriam wrote: You're right. It solved my immediate challenge but I was going to get stuck in the 2nd part. Can you please tell me how to tie it to the worksheet change event.? Thanks! "Jerry W. Lewis" wrote: NA() will prevent a plot symbol, but will not break the line if there are actual values on either side of it. If you need that latter behavior, your only option is to delete the formula. You could automate the process of deleting and restoring the formula by tying it to the worksheet change event. Jerry Myriam wrote: I found the solution in K. Georgeatis formula, back in May: =IF(ISNUMBER(A1),A1,NA()) Thanks for posting it! "Myriam" wrote: I have a data table that pulls information from other places in my workbook. Therefore, cells are not empty. I have a simple line chart and I want to have breaks on the line if there is no data on a particular cell. However, if I don't have data it plots it as zero. Any suggestions? Thanks! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#8
|
|||
|
|||
Debra,
I just now read your post! You were absolutely right. Definitely worth a visit!!Thanks! "Debra Dalgleish" wrote: The link to Andy Pope's site is: http://andypope.info/ It's definitely worth a visit! Myriam wrote: Jon, Thanks for the links. The first one, I couldn't open. Tushar-mehta's add-in is fantastic! "Jon Peltier" wrote: Myriam - Both Andy Pope (http://andypopo.info) and Tushar Mehta (http://tushar-mehta.com) have solutions on their web sites to the lack of a gap in the line where there's an #N/A error value. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Myriam wrote: You're right. It solved my immediate challenge but I was going to get stuck in the 2nd part. Can you please tell me how to tie it to the worksheet change event.? Thanks! "Jerry W. Lewis" wrote: NA() will prevent a plot symbol, but will not break the line if there are actual values on either side of it. If you need that latter behavior, your only option is to delete the formula. You could automate the process of deleting and restoring the formula by tying it to the worksheet change event. Jerry Myriam wrote: I found the solution in K. Georgeatis formula, back in May: =IF(ISNUMBER(A1),A1,NA()) Thanks for posting it! "Myriam" wrote: I have a data table that pulls information from other places in my workbook. Therefore, cells are not empty. I have a simple line chart and I want to have breaks on the line if there is no data on a particular cell. However, if I don't have data it plots it as zero. Any suggestions? Thanks! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#9
|
|||
|
|||
You're welcome! I'm glad you found it.
Myriam wrote: Debra, I just now read your post! You were absolutely right. Definitely worth a visit!!Thanks! "Debra Dalgleish" wrote: The link to Andy Pope's site is: http://andypope.info/ It's definitely worth a visit! Myriam wrote: Jon, Thanks for the links. The first one, I couldn't open. Tushar-mehta's add-in is fantastic! "Jon Peltier" wrote: Myriam - Both Andy Pope (http://andypopo.info) and Tushar Mehta (http://tushar-mehta.com) have solutions on their web sites to the lack of a gap in the line where there's an #N/A error value. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Myriam wrote: You're right. It solved my immediate challenge but I was going to get stuck in the 2nd part. Can you please tell me how to tie it to the worksheet change event.? Thanks! "Jerry W. Lewis" wrote: NA() will prevent a plot symbol, but will not break the line if there are actual values on either side of it. If you need that latter behavior, your only option is to delete the formula. You could automate the process of deleting and restoring the formula by tying it to the worksheet change event. Jerry Myriam wrote: I found the solution in K. Georgeatis formula, back in May: =IF(ISNUMBER(A1),A1,NA()) Thanks for posting it! "Myriam" wrote: I have a data table that pulls information from other places in my workbook. Therefore, cells are not empty. I have a simple line chart and I want to have breaks on the line if there is no data on a particular cell. However, if I don't have data it plots it as zero. Any suggestions? Thanks! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to force continuous lines in line charts | Phillip Earthlink net> | Charts and Charting | 5 | December 21st, 2004 10:43 PM |
Dynamic Labels for Line Charts | Smooth | Charts and Charting | 2 | May 26th, 2004 04:09 AM |
Line charts | Debra Dalgleish | Charts and Charting | 0 | December 19th, 2003 11:13 AM |
Line charts | Anne | Charts and Charting | 0 | December 19th, 2003 08:01 AM |
Combination line and stacked bar charts | Ian | Charts and Charting | 2 | October 15th, 2003 05:28 PM |