Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 38
Default How do I bridge the gaps in data in (XL 2007) Area Chart

XL 2007 doesn't have the TOOLS-Options-Interpolate menu and choices.
This goes for a line chart as well.
Thx
  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 1,104
Default How do I bridge the gaps in data in (XL 2007) Area Chart

Click the chart to activate it
In the Design tab of Chart Tools, click Select Data
A dialog box opens, look at lower left corner for "Hidden and Empty Cells"
button and click it
You get three options: gaps, zero and connect.

Alternative (works in all version of Excel); in the empty cell type =NA().
It will display as #N/A and will be ignored by the chart engine

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"BABs" wrote in message
...
XL 2007 doesn't have the TOOLS-Options-Interpolate menu and choices.
This goes for a line chart as well.
Thx



  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 38
Default How do I bridge the gaps in data in (XL 2007) Area Chart

Bernard,
That worked for bridging the gaps in the line plot, but not the area chart.
The area chart has the "connect" grayed out. Also, if I enter =na(), it
treats them as 0's and doesn't interpolate.
Any suggestions?






"Bernard Liengme" wrote:

Click the chart to activate it
In the Design tab of Chart Tools, click Select Data
A dialog box opens, look at lower left corner for "Hidden and Empty Cells"
button and click it
You get three options: gaps, zero and connect.

Alternative (works in all version of Excel); in the empty cell type =NA().
It will display as #N/A and will be ignored by the chart engine

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"BABs" wrote in message
...
XL 2007 doesn't have the TOOLS-Options-Interpolate menu and choices.
This goes for a line chart as well.
Thx




  #4   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 1,104
Default How do I bridge the gaps in data in (XL 2007) Area Chart

Sorry, I cannot help. Let's hope Jon see you question.
I never use Area charts and I failed to see this in the subject line --
sorry!!!
Suggest you post the question again
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"BABs" wrote in message
...
Bernard,
That worked for bridging the gaps in the line plot, but not the area
chart.
The area chart has the "connect" grayed out. Also, if I enter =na(), it
treats them as 0's and doesn't interpolate.
Any suggestions?






"Bernard Liengme" wrote:

Click the chart to activate it
In the Design tab of Chart Tools, click Select Data
A dialog box opens, look at lower left corner for "Hidden and Empty
Cells"
button and click it
You get three options: gaps, zero and connect.

Alternative (works in all version of Excel); in the empty cell type
=NA().
It will display as #N/A and will be ignored by the chart engine

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"BABs" wrote in message
...
XL 2007 doesn't have the TOOLS-Options-Interpolate menu and choices.
This goes for a line chart as well.
Thx






  #5   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 24
Default How do I bridge the gaps in data in (XL 2007) Area Chart

On Tue, 9 Jun 2009 12:38:01 -0700, BABs
wrote:

Bernard,
That worked for bridging the gaps in the line plot, but not the area chart.
The area chart has the "connect" grayed out. Also, if I enter =na(), it
treats them as 0's and doesn't interpolate.
Any suggestions?




Hiding the row removes it from any chart referring to that data range
the row is hidden within.



"Bernard Liengme" wrote:

Click the chart to activate it
In the Design tab of Chart Tools, click Select Data
A dialog box opens, look at lower left corner for "Hidden and Empty Cells"
button and click it
You get three options: gaps, zero and connect.

Alternative (works in all version of Excel); in the empty cell type =NA().
It will display as #N/A and will be ignored by the chart engine

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"BABs" wrote in message
...
XL 2007 doesn't have the TOOLS-Options-Interpolate menu and choices.
This goes for a line chart as well.
Thx






  #6   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2,489
Default How do I bridge the gaps in data in (XL 2007) Area Chart

Hi,

The NA() trick does/did not work with area charts.
xl2003: the gap in data results in a zero value. The Empty cells options of
Gap/Interpolate are the same, zero.

xl2007: the gap plotted as zero is as expected. Plotted as gap produces
shear drops between gaps.

The only way to fill the gap is to use another series to plot the missing
values. You will need formula to calculate the interpolated values.

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"BABs" wrote in message
...
Bernard,
That worked for bridging the gaps in the line plot, but not the area
chart.
The area chart has the "connect" grayed out. Also, if I enter =na(), it
treats them as 0's and doesn't interpolate.
Any suggestions?






"Bernard Liengme" wrote:

Click the chart to activate it
In the Design tab of Chart Tools, click Select Data
A dialog box opens, look at lower left corner for "Hidden and Empty
Cells"
button and click it
You get three options: gaps, zero and connect.

Alternative (works in all version of Excel); in the empty cell type
=NA().
It will display as #N/A and will be ignored by the chart engine

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"BABs" wrote in message
...
XL 2007 doesn't have the TOOLS-Options-Interpolate menu and choices.
This goes for a line chart as well.
Thx





  #7   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default How do I bridge the gaps in data in (XL 2007) Area Chart

If you have to use an area chart, and hiding rows isn't an option, you need
to insert a formula into the blank cell which interpolates between the
values on either side.


Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html


- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Bernard Liengme" wrote in message
...
Sorry, I cannot help. Let's hope Jon see you question.
I never use Area charts and I failed to see this in the subject line --
sorry!!!
Suggest you post the question again
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"BABs" wrote in message
...
Bernard,
That worked for bridging the gaps in the line plot, but not the area
chart.
The area chart has the "connect" grayed out. Also, if I enter =na(), it
treats them as 0's and doesn't interpolate.
Any suggestions?






"Bernard Liengme" wrote:

Click the chart to activate it
In the Design tab of Chart Tools, click Select Data
A dialog box opens, look at lower left corner for "Hidden and Empty
Cells"
button and click it
You get three options: gaps, zero and connect.

Alternative (works in all version of Excel); in the empty cell type
=NA().
It will display as #N/A and will be ignored by the chart engine

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"BABs" wrote in message
...
XL 2007 doesn't have the TOOLS-Options-Interpolate menu and choices.
This goes for a line chart as well.
Thx







  #8   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default How do I bridge the gaps in data in (XL 2007) Area Chart

Want to see something fun? Make a line chart with a missing value in the
middle, set the option to connect points across a gap, then convert to an
unstacked area chart. Where the area chart would have shown a gap with
vertical sides, now the area is filled up to the top of the chart between
vertical sides. If you copy the chart, the copy treats the gap as a zero,
and when you change back to gap, you get the gap to the bottom, not the
block to the top.

Only the unstacked area charts give you a gap with vertical sides (and as I
mentioned, the copy of one has its option reset to zero). The stacked ones
can only treat the gap as zero.


Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html


- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Andy Pope" wrote in message
...
Hi,

The NA() trick does/did not work with area charts.
xl2003: the gap in data results in a zero value. The Empty cells options
of Gap/Interpolate are the same, zero.

xl2007: the gap plotted as zero is as expected. Plotted as gap produces
shear drops between gaps.

The only way to fill the gap is to use another series to plot the missing
values. You will need formula to calculate the interpolated values.

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"BABs" wrote in message
...
Bernard,
That worked for bridging the gaps in the line plot, but not the area
chart.
The area chart has the "connect" grayed out. Also, if I enter =na(), it
treats them as 0's and doesn't interpolate.
Any suggestions?






"Bernard Liengme" wrote:

Click the chart to activate it
In the Design tab of Chart Tools, click Select Data
A dialog box opens, look at lower left corner for "Hidden and Empty
Cells"
button and click it
You get three options: gaps, zero and connect.

Alternative (works in all version of Excel); in the empty cell type
=NA().
It will display as #N/A and will be ignored by the chart engine

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"BABs" wrote in message
...
XL 2007 doesn't have the TOOLS-Options-Interpolate menu and choices.
This goes for a line chart as well.
Thx






  #9   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2,489
Default How do I bridge the gaps in data in (XL 2007) Area Chart

Yes I saw that when testing. I guess the copy process doesn't check invalid
settings and uses defaults instead.

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Jon Peltier" wrote in message
...
Want to see something fun? Make a line chart with a missing value in the
middle, set the option to connect points across a gap, then convert to an
unstacked area chart. Where the area chart would have shown a gap with
vertical sides, now the area is filled up to the top of the chart between
vertical sides. If you copy the chart, the copy treats the gap as a zero,
and when you change back to gap, you get the gap to the bottom, not the
block to the top.

Only the unstacked area charts give you a gap with vertical sides (and as
I mentioned, the copy of one has its option reset to zero). The stacked
ones can only treat the gap as zero.


Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html


- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Andy Pope" wrote in message
...
Hi,

The NA() trick does/did not work with area charts.
xl2003: the gap in data results in a zero value. The Empty cells options
of Gap/Interpolate are the same, zero.

xl2007: the gap plotted as zero is as expected. Plotted as gap produces
shear drops between gaps.

The only way to fill the gap is to use another series to plot the missing
values. You will need formula to calculate the interpolated values.

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"BABs" wrote in message
...
Bernard,
That worked for bridging the gaps in the line plot, but not the area
chart.
The area chart has the "connect" grayed out. Also, if I enter =na(), it
treats them as 0's and doesn't interpolate.
Any suggestions?






"Bernard Liengme" wrote:

Click the chart to activate it
In the Design tab of Chart Tools, click Select Data
A dialog box opens, look at lower left corner for "Hidden and Empty
Cells"
button and click it
You get three options: gaps, zero and connect.

Alternative (works in all version of Excel); in the empty cell type
=NA().
It will display as #N/A and will be ignored by the chart engine

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"BABs" wrote in message
...
XL 2007 doesn't have the TOOLS-Options-Interpolate menu and choices.
This goes for a line chart as well.
Thx







  #10   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2
Default How do I bridge the gaps in data in (XL 2007) Area Chart

I hope someone is still reviewing this thread. I'm trying to create a stacked
line chart that has a lot of zero's here and there. When I access the "Hidden
and Empty Cells" dialogue box the only active choice I have is "zero" - the
choices for gaps and lines are grayed out. What do I have to do to get all of
the choices?

"Jon Peltier" wrote:

Want to see something fun? Make a line chart with a missing value in the
middle, set the option to connect points across a gap, then convert to an
unstacked area chart. Where the area chart would have shown a gap with
vertical sides, now the area is filled up to the top of the chart between
vertical sides. If you copy the chart, the copy treats the gap as a zero,
and when you change back to gap, you get the gap to the bottom, not the
block to the top.

Only the unstacked area charts give you a gap with vertical sides (and as I
mentioned, the copy of one has its option reset to zero). The stacked ones
can only treat the gap as zero.


Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html


- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Andy Pope" wrote in message
...
Hi,

The NA() trick does/did not work with area charts.
xl2003: the gap in data results in a zero value. The Empty cells options
of Gap/Interpolate are the same, zero.

xl2007: the gap plotted as zero is as expected. Plotted as gap produces
shear drops between gaps.

The only way to fill the gap is to use another series to plot the missing
values. You will need formula to calculate the interpolated values.

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"BABs" wrote in message
...
Bernard,
That worked for bridging the gaps in the line plot, but not the area
chart.
The area chart has the "connect" grayed out. Also, if I enter =na(), it
treats them as 0's and doesn't interpolate.
Any suggestions?






"Bernard Liengme" wrote:

Click the chart to activate it
In the Design tab of Chart Tools, click Select Data
A dialog box opens, look at lower left corner for "Hidden and Empty
Cells"
button and click it
You get three options: gaps, zero and connect.

Alternative (works in all version of Excel); in the empty cell type
=NA().
It will display as #N/A and will be ignored by the chart engine

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"BABs" wrote in message
...
XL 2007 doesn't have the TOOLS-Options-Interpolate menu and choices.
This goes for a line chart as well.
Thx









  #11   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 461
Default How do I bridge the gaps in data in (XL 2007) Area Chart

Do all the gaps line up? If not, you won't get the stacked appearance
that I think you're expecting. I figured a stacked line chart wouldn't
offer gaps or interpolated lines for this reason. I was wrong, at least
in 2003. The options are all offered, but whatever is selected, the
chart treats the blank cells as zero values.

In any case, the setting is for blank cells, not for cells containing
any kind of formula or for cells containing zero values.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



Laurie Foss wrote:
I hope someone is still reviewing this thread. I'm trying to create a stacked
line chart that has a lot of zero's here and there. When I access the "Hidden
and Empty Cells" dialogue box the only active choice I have is "zero" - the
choices for gaps and lines are grayed out. What do I have to do to get all of
the choices?

"Jon Peltier" wrote:

Want to see something fun? Make a line chart with a missing value in the
middle, set the option to connect points across a gap, then convert to an
unstacked area chart. Where the area chart would have shown a gap with
vertical sides, now the area is filled up to the top of the chart between
vertical sides. If you copy the chart, the copy treats the gap as a zero,
and when you change back to gap, you get the gap to the bottom, not the
block to the top.

Only the unstacked area charts give you a gap with vertical sides (and as I
mentioned, the copy of one has its option reset to zero). The stacked ones
can only treat the gap as zero.


Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html


- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Andy Pope" wrote in message
...
Hi,

The NA() trick does/did not work with area charts.
xl2003: the gap in data results in a zero value. The Empty cells options
of Gap/Interpolate are the same, zero.

xl2007: the gap plotted as zero is as expected. Plotted as gap produces
shear drops between gaps.

The only way to fill the gap is to use another series to plot the missing
values. You will need formula to calculate the interpolated values.

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"BABs" wrote in message
...
Bernard,
That worked for bridging the gaps in the line plot, but not the area
chart.
The area chart has the "connect" grayed out. Also, if I enter =na(), it
treats them as 0's and doesn't interpolate.
Any suggestions?






"Bernard Liengme" wrote:

Click the chart to activate it
In the Design tab of Chart Tools, click Select Data
A dialog box opens, look at lower left corner for "Hidden and Empty
Cells"
button and click it
You get three options: gaps, zero and connect.

Alternative (works in all version of Excel); in the empty cell type
=NA().
It will display as #N/A and will be ignored by the chart engine

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"BABs" wrote in message
...
XL 2007 doesn't have the TOOLS-Options-Interpolate menu and choices.
This goes for a line chart as well.
Thx




  #12   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2
Default How do I bridge the gaps in data in (XL 2007) Area Chart

Jon,
I finally figured out why I wasn't able to access the options in the hidden
and empty cells dialogue box. The stacked line chart doesn't support it. I
tried playing around with several different styles of charts and when I
changed to a simple line chart the options were available.
Thanks for the reply

Laurie Foss

"Jon Peltier" wrote:

Do all the gaps line up? If not, you won't get the stacked appearance
that I think you're expecting. I figured a stacked line chart wouldn't
offer gaps or interpolated lines for this reason. I was wrong, at least
in 2003. The options are all offered, but whatever is selected, the
chart treats the blank cells as zero values.

In any case, the setting is for blank cells, not for cells containing
any kind of formula or for cells containing zero values.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



Laurie Foss wrote:
I hope someone is still reviewing this thread. I'm trying to create a stacked
line chart that has a lot of zero's here and there. When I access the "Hidden
and Empty Cells" dialogue box the only active choice I have is "zero" - the
choices for gaps and lines are grayed out. What do I have to do to get all of
the choices?


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
How to create a bridge chart in excel? Bill Charts and Charting in Excel 3 May 7th 23 11:41 AM
scatter chart with gaps in series (XL 2007) Boris Charts and Charting in Excel 7 March 2nd 09 05:44 PM
bridge chart billg Charts and Charting in Excel 1 February 14th 09 10:06 PM
Bridge Chart Paula Charts and Charting in Excel 1 April 29th 08 10:28 PM
Stacked chart with data gaps Greg G[_2_] Charts and Charting in Excel 3 April 5th 07 09:38 PM


All times are GMT +1. The time now is 09:58 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"