|
| 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
June 9th, 2009, 07:45 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
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
June 9th, 2009, 08:23 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
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
June 9th, 2009, 08:38 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
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
June 9th, 2009, 08:55 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
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
June 10th, 2009, 01:41 AM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
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
June 10th, 2009, 09:24 AM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
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
June 11th, 2009, 01:59 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
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
June 11th, 2009, 02:08 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
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
June 11th, 2009, 02:21 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
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
August 12th, 2009, 06:08 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
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
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
