Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 24th 07, 11:21 PM posted to microsoft.public.excel.charting
RW RW is offline
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 49
Default Lines in chart when no data is present

I have found the trick to prevent 0's from showing in a chart when the data
is blank:

If(H2="",NA(),H2/F2)

However, the chart line extrapolates from the previous data point to the
next datapoint instead of showing a gap. I've looked at Tools-Options-Chart
and the Active Chart - Plot Empty Cells as "Not Plotted (leave gaps)" is
selected.

The data is being charted on a secondary Y axis if this makes a difference.
Please help. I need to have gaps.

  #2   Report Post  
Old January 25th 07, 12:36 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 2,344
Default Lines in chart when no data is present

Hi RW,

I don't believe there is a straight forward way to do this.

--
Cheers,
Shane Devenshire


"RW" wrote:

I have found the trick to prevent 0's from showing in a chart when the data
is blank:

If(H2="",NA(),H2/F2)

However, the chart line extrapolates from the previous data point to the
next datapoint instead of showing a gap. I've looked at Tools-Options-Chart
and the Active Chart - Plot Empty Cells as "Not Plotted (leave gaps)" is
selected.

The data is being charted on a secondary Y axis if this makes a difference.
Please help. I need to have gaps.

  #3   Report Post  
Old January 25th 07, 01:43 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default Lines in chart when no data is present

Nothing straightforward, but Andy Pope has a technique for this:

http://andypope.info/charts/brokenlines.htm

Other alternatives include deleting the cells to form a gap (via macro which
can recreate the formula.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"ShaneDevenshire" wrote in
message ...
Hi RW,

I don't believe there is a straight forward way to do this.

--
Cheers,
Shane Devenshire


"RW" wrote:

I have found the trick to prevent 0's from showing in a chart when the
data
is blank:

If(H2="",NA(),H2/F2)

However, the chart line extrapolates from the previous data point to the
next datapoint instead of showing a gap. I've looked at
Tools-Options-Chart
and the Active Chart - Plot Empty Cells as "Not Plotted (leave gaps)" is
selected.

The data is being charted on a secondary Y axis if this makes a
difference.
Please help. I need to have gaps.



  #4   Report Post  
Old January 25th 07, 01:56 AM posted to microsoft.public.excel.charting
RW RW is offline
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 49
Default Lines in chart when no data is present

According to the following link,
http://office.microsoft.com/en-us/ex...346081033.aspx

it states:
"By default, Excel does not plot a data point for an empty cell, which
causes a break in a data series. "

and even shows an example. Why does it not work for my data?

"ShaneDevenshire" wrote:

Hi RW,

I don't believe there is a straight forward way to do this.

--
Cheers,
Shane Devenshire


"RW" wrote:

I have found the trick to prevent 0's from showing in a chart when the data
is blank:

If(H2="",NA(),H2/F2)

However, the chart line extrapolates from the previous data point to the
next datapoint instead of showing a gap. I've looked at Tools-Options-Chart
and the Active Chart - Plot Empty Cells as "Not Plotted (leave gaps)" is
selected.

The data is being charted on a secondary Y axis if this makes a difference.
Please help. I need to have gaps.

  #5   Report Post  
Old January 25th 07, 02:28 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default Lines in chart when no data is present

Because you don't have empty cells, you have formulas that return "", which
is a short piece of text. Unfortunately there is no function that simulates
a blank cell.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"RW" wrote in message
...
According to the following link,
http://office.microsoft.com/en-us/ex...346081033.aspx

it states:
"By default, Excel does not plot a data point for an empty cell, which
causes a break in a data series. "

and even shows an example. Why does it not work for my data?

"ShaneDevenshire" wrote:

Hi RW,

I don't believe there is a straight forward way to do this.

--
Cheers,
Shane Devenshire


"RW" wrote:

I have found the trick to prevent 0's from showing in a chart when the
data
is blank:

If(H2="",NA(),H2/F2)

However, the chart line extrapolates from the previous data point to
the
next datapoint instead of showing a gap. I've looked at
Tools-Options-Chart
and the Active Chart - Plot Empty Cells as "Not Plotted (leave gaps)"
is
selected.

The data is being charted on a secondary Y axis if this makes a
difference.
Please help. I need to have gaps.





  #6   Report Post  
Old January 25th 07, 05:39 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 2,344
Default Lines in chart when no data is present

By the way I also have a way to do it but its not pretty. There is also a
code solution which might be simplier but you would have to run it anytime
the data changes.
--
Thanks,
Shane Devenshire


"Jon Peltier" wrote:

Because you don't have empty cells, you have formulas that return "", which
is a short piece of text. Unfortunately there is no function that simulates
a blank cell.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"RW" wrote in message
...
According to the following link,
http://office.microsoft.com/en-us/ex...346081033.aspx

it states:
"By default, Excel does not plot a data point for an empty cell, which
causes a break in a data series. "

and even shows an example. Why does it not work for my data?

"ShaneDevenshire" wrote:

Hi RW,

I don't believe there is a straight forward way to do this.

--
Cheers,
Shane Devenshire


"RW" wrote:

I have found the trick to prevent 0's from showing in a chart when the
data
is blank:

If(H2="",NA(),H2/F2)

However, the chart line extrapolates from the previous data point to
the
next datapoint instead of showing a gap. I've looked at
Tools-Options-Chart
and the Active Chart - Plot Empty Cells as "Not Plotted (leave gaps)"
is
selected.

The data is being charted on a secondary Y axis if this makes a
difference.
Please help. I need to have gaps.




  #7   Report Post  
Old January 25th 07, 02:15 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,071
Default Lines in chart when no data is present

That's the way MS designed the charting capability. If you are plotting a
XY Scatter chart, check out the add-in at
Chart gap for N/A
http://www.tushar-mehta.com/excel/so...ity/index.html

It used to work with Line charts until MS made a change that messed up how
Line charts deal with breaks in the line.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I have found the trick to prevent 0's from showing in a chart when the data
is blank:

If(H2="",NA(),H2/F2)

However, the chart line extrapolates from the previous data point to the
next datapoint instead of showing a gap. I've looked at Tools-Options-Chart
and the Active Chart - Plot Empty Cells as "Not Plotted (leave gaps)" is
selected.

The data is being charted on a secondary Y axis if this makes a difference.
Please help. I need to have gaps.

  #8   Report Post  
Old January 25th 07, 03:19 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default Lines in chart when no data is present

Tushar -

In which version of Excel did Microsoft make the change?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Tushar Mehta" wrote in message
om...
That's the way MS designed the charting capability. If you are plotting a
XY Scatter chart, check out the add-in at
Chart gap for N/A
http://www.tushar-mehta.com/excel/so...ity/index.html

It used to work with Line charts until MS made a change that messed up how
Line charts deal with breaks in the line.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I have found the trick to prevent 0's from showing in a chart when the
data
is blank:

If(H2="",NA(),H2/F2)

However, the chart line extrapolates from the previous data point to the
next datapoint instead of showing a gap. I've looked at
Tools-Options-Chart
and the Active Chart - Plot Empty Cells as "Not Plotted (leave gaps)" is
selected.

The data is being charted on a secondary Y axis if this makes a
difference.
Please help. I need to have gaps.



  #9   Report Post  
Old January 25th 07, 04:06 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,071
Default Lines in chart when no data is present

Hi Jon,

I just retested versions of XL from 2000 to 2007: created a Line chart and
then tried to format a single point to 'no line'. As of today, in 2002
(SP2?) it still shows the line. I am reasonably sure that was the same
behavior with 2003 when it first came out but today the line disappeared for
the single point. So, it was probably fixed in some 2003 SP.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Tushar -

In which version of Excel did Microsoft make the change?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Tushar Mehta" wrote in message
om...
That's the way MS designed the charting capability. If you are plotting a
XY Scatter chart, check out the add-in at
Chart gap for N/A
http://www.tushar-mehta.com/excel/so...ity/index.html

It used to work with Line charts until MS made a change that messed up how
Line charts deal with breaks in the line.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I have found the trick to prevent 0's from showing in a chart when the
data
is blank:

If(H2="",NA(),H2/F2)

However, the chart line extrapolates from the previous data point to the
next datapoint instead of showing a gap. I've looked at
Tools-Options-Chart
and the Active Chart - Plot Empty Cells as "Not Plotted (leave gaps)" is
selected.

The data is being charted on a secondary Y axis if this makes a
difference.
Please help. I need to have gaps.




  #10   Report Post  
Old January 25th 07, 06:03 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default Lines in chart when no data is present

This is vaguely familiar (everything before yesterday is only vaguely
familiar).

I just tested Excel 2003 SP2, and the line can be formatted as no line
without a problem. I recall that the issue was that instead of making the
line segment vanish, what Excel did was draw the line from the previous
point to the subsequent point, so the point in the middle was still there
but not connected to either adjacent point. I wonder if that was how Excel
2003 came out, and they fixed it (i.e., changed it back) in an SP.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Tushar Mehta" wrote in message
om...
Hi Jon,

I just retested versions of XL from 2000 to 2007: created a Line chart and
then tried to format a single point to 'no line'. As of today, in 2002
(SP2?) it still shows the line. I am reasonably sure that was the same
behavior with 2003 when it first came out but today the line disappeared
for
the single point. So, it was probably fixed in some 2003 SP.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Tushar -

In which version of Excel did Microsoft make the change?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Tushar Mehta" wrote in message
om...
That's the way MS designed the charting capability. If you are
plotting a
XY Scatter chart, check out the add-in at
Chart gap for N/A
http://www.tushar-mehta.com/excel/so...ity/index.html

It used to work with Line charts until MS made a change that messed up
how
Line charts deal with breaks in the line.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I have found the trick to prevent 0's from showing in a chart when the
data
is blank:

If(H2="",NA(),H2/F2)

However, the chart line extrapolates from the previous data point to
the
next datapoint instead of showing a gap. I've looked at
Tools-Options-Chart
and the Active Chart - Plot Empty Cells as "Not Plotted (leave gaps)"
is
selected.

The data is being charted on a secondary Y axis if this makes a
difference.
Please help. I need to have gaps.








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
Create single chart with data from seperate worksheets MATT Charts and Charting in Excel 1 May 10th 06 04:09 AM
Can't get data from a web page to chart in excel. Any solutions? goody444 Charts and Charting in Excel 1 March 1st 06 02:23 PM
Can't get data from a web page to chart in excel. Any solutions? goody444 Excel Discussion (Misc queries) 1 March 1st 06 07:09 AM
Excel Macro to Copy & Paste [email protected] Excel Worksheet Functions 0 December 1st 05 02:56 PM
Help making a chart that doesn't graph cells without data? Filtration Guy Excel Discussion (Misc queries) 0 March 22nd 05 10:21 PM


All times are GMT +1. The time now is 08:32 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017