In Excel 2007, I would like my chart to not display chart data for empty cells.

On the "Hidden and Empty Cell Settings" I have "Show empty cells as: Gaps".
I can't seem to get this to work, but my cells aren't empty perse. They
return a formula value:

=IF(<condition=0,"",<formula)

The cells that are being displayed (as 0) on the chart appear blank in the
spreadsheet. Any ideas?

"" isn't a blank, as you're figuring out. It's text, which Excel interprets
as zero. You can't make a chart show a gap in a cell that isn't blank, but
if you use NA() instead of "",

=IF(<condition=0,NA(),<formula)

The cell gets a big ugly #N/A error, which is not plotted in a line or XY
chart. If the chart has markers connected with lines, there will be no
marker where there is #N/A, and the line will be interpolated across where
the gap would have been.

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


"Brandon" <crimson"underscore"m"at"hotmail.com wrote in message
...
In Excel 2007, I would like my chart to not display chart data for empty
cells.

On the "Hidden and Empty Cell Settings" I have "Show empty cells as:
Gaps".
I can't seem to get this to work, but my cells aren't empty perse. They
return a formula value:

=IF(<condition=0,"",<formula)

The cells that are being displayed (as 0) on the chart appear blank in the
spreadsheet. Any ideas?

Hi,

Use NA() instead of "" which will return a #N'A instead of a empty string.
Your chart will not plot the #N/A

Dave

url:http://www.ureader.com/msg/10296332.aspx

I have the same problem. I tried using NA() but it displayts #NA as a column
in chart. Any suggestion ?

NA() works for line and XY charts. Try "" for column or bar charts.

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


"@bhi" wrote in message
...
I have the same problem. I tried using NA() but it displayts #NA as a
column
in chart. Any suggestion ?

I tried all 3
(1) NA() i.e #NA
(2) ""
(3) 0

but it creates bar for all of them.


"Jon Peltier" wrote:

NA() works for line and XY charts. Try "" for column or bar charts.

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


"@bhi" wrote in message
...
I have the same problem. I tried using NA() but it displayts #NA as a
column
in chart. Any suggestion ?

It creates a bar, or it creates a space where the bar would go?

Maybe you should paste your data into a reply.

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


"@bhi" wrote in message
...
I tried all 3
(1) NA() i.e #NA
(2) ""
(3) 0

but it creates bar for all of them.


"Jon Peltier" wrote:

NA() works for line and XY charts. Try "" for column or bar charts.

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


"@bhi" wrote in message
...
I have the same problem. I tried using NA() but it displayts #NA as a
column
in chart. Any suggestion ?

Yes it creates space where bar would go for all NA() (#NA) or 0.
For example I am using similar data same as bellow format
(both year and sales will come on runtime but will not be more than 10 values)

Year Sales
2001 100
2002 200
2003 300
2004 400
2005 500
#NA #NA
#NA #NA
#NA #NA
#NA #NA

so here years and its data can come from 2001 to 2010 depend upon user
selected criteria. I want to draw chart only for the which value exist.




I am using

"Jon Peltier" wrote:

It creates a bar, or it creates a space where the bar would go?

Maybe you should paste your data into a reply.

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


"@bhi" wrote in message
...
I tried all 3
(1) NA() i.e #NA
(2) ""
(3) 0

but it creates bar for all of them.


"Jon Peltier" wrote:

NA() works for line and XY charts. Try "" for column or bar charts.

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


"@bhi" wrote in message
...
I have the same problem. I tried using NA() but it displayts #NA as a
column
in chart. Any suggestion ?

Are the unwanted cells only at the end of the range? Then define a named
range that is as long as the number of rows with data.

http://peltiertech.com/WordPress/200...ynamic-charts/
http://peltiertech.com/Excel/Charts/Dynamics.html

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


"@bhi" wrote in message
...
Yes it creates space where bar would go for all NA() (#NA) or 0.
For example I am using similar data same as bellow format
(both year and sales will come on runtime but will not be more than 10
values)

Year Sales
2001 100
2002 200
2003 300
2004 400
2005 500
#NA #NA
#NA #NA
#NA #NA
#NA #NA

so here years and its data can come from 2001 to 2010 depend upon user
selected criteria. I want to draw chart only for the which value exist.




I am using

"Jon Peltier" wrote:

It creates a bar, or it creates a space where the bar would go?

Maybe you should paste your data into a reply.

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


"@bhi" wrote in message
...
I tried all 3
(1) NA() i.e #NA
(2) ""
(3) 0

but it creates bar for all of them.


"Jon Peltier" wrote:

NA() works for line and XY charts. Try "" for column or bar charts.

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


"@bhi" wrote in message
...
I have the same problem. I tried using NA() but it displayts #NA as a
column
in chart. Any suggestion ?

Jon,

In your example you said that:

=IF(<condition=0,NA(),<formula)

will cause Excel to interpolate a line across where the chart gap would have been.

This is good functionality, but if I want to have Excel instead actually leave the gap between two markers and NOT interpolate the line, how do I do this. In other words I would like the line chart data series to appear disjointed. The chart series is calculated so there is no problem incorporating formulae such as the above.

Also if you would not mind a tangential question, with Excel 2007 is there any way to force an added trend line to appear on the chart visually "behind" the series it is trending, sort of like a z-order or z-index in programming? The default seems to be that it appears on top of the series line and for my use it would be cleaner to have it appear behind instead.

Thanks in advance!

- Daniel Ferry



Jon Peltier wrote:

"" isn't a blank, as you're figuring out.
01-Jul-08

"" isn't a blank, as you're figuring out. It's text, which Excel interprets
as zero. You can't make a chart show a gap in a cell that isn't blank, but
if you use NA() instead of "",

=IF(<condition=0,NA(),<formula)

The cell gets a big ugly #N/A error, which is not plotted in a line or XY
chart. If the chart has markers connected with lines, there will be no
marker where there is #N/A, and the line will be interpolated across where
the gap would have been.

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


"Brandon" <crimson"underscore"m"at"hotmail.com wrote in message
...

Previous Posts In This Thread:

On Tuesday, July 01, 2008 10:11 PM
crimson"underscore"m"at"hotmail.com wrote:

Chart displaying "blank" cells?
In Excel 2007, I would like my chart to not display chart data for empty cells.

On the "Hidden and Empty Cell Settings" I have "Show empty cells as: Gaps".
I can't seem to get this to work, but my cells aren't empty perse. They
return a formula value:

=IF(<condition=0,"",<formula)

The cells that are being displayed (as 0) on the chart appear blank in the
spreadsheet. Any ideas?

On Tuesday, July 01, 2008 11:49 PM
Jon Peltier wrote:

"" isn't a blank, as you're figuring out.
"" isn't a blank, as you're figuring out. It's text, which Excel interprets
as zero. You can't make a chart show a gap in a cell that isn't blank, but
if you use NA() instead of "",

=IF(<condition=0,NA(),<formula)

The cell gets a big ugly #N/A error, which is not plotted in a line or XY
chart. If the chart has markers connected with lines, there will be no
marker where there is #N/A, and the line will be interpolated across where
the gap would have been.

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


"Brandon" <crimson"underscore"m"at"hotmail.com wrote in message
...

On Wednesday, July 02, 2008 3:04 AM
Dave Curtis wrote:

Chart displaying "blank" cells?
Hi,

Use NA() instead of "" which will return a #N'A instead of a empty string.
Your chart will not plot the #N/A

Dave

url:http://www.ureader.com/msg/10296332.aspx

On Friday, July 11, 2008 11:45 AM
wrote:

Chart displaying "blank" cells?
I have the same problem. I tried using NA() but it displayts #NA as a column
in chart. Any suggestion ?

On Saturday, July 12, 2008 11:16 AM
Jon Peltier wrote:

NA() works for line and XY charts. Try "" for column or bar charts.
NA() works for line and XY charts. Try "" for column or bar charts.

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


"@bhi" wrote in message
...

On Saturday, July 12, 2008 12:29 PM
wrote:

I tried all 3(1) NA() i.e #NA(2) ""(3) 0but it creates bar for all of them.
I tried all 3
(1) NA() i.e #NA
(2) ""
(3) 0

but it creates bar for all of them.


"Jon Peltier" wrote:

On Saturday, July 12, 2008 12:53 PM
Jon Peltier wrote:

It creates a bar, or it creates a space where the bar would go?
It creates a bar, or it creates a space where the bar would go?

Maybe you should paste your data into a reply.

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


"@bhi" wrote in message
...

On Monday, July 14, 2008 8:19 PM
bh wrote:

Yes it creates space where bar would go for all NA() (#NA) or 0.
Yes it creates space where bar would go for all NA() (#NA) or 0.
For example I am using similar data same as bellow format
(both year and sales will come on runtime but will not be more than 10 values)

Year Sales
2001 100
2002 200
2003 300
2004 400
2005 500

so here years and its data can come from 2001 to 2010 depend upon user
selected criteria. I want to draw chart only for the which value exist.




I am using

"Jon Peltier" wrote:

On Tuesday, July 15, 2008 7:06 AM
Jon Peltier wrote:

Are the unwanted cells only at the end of the range?
Are the unwanted cells only at the end of the range? Then define a named
range that is as long as the number of rows with data.

http://peltiertech.com/WordPress/200...ynamic-charts/
http://peltiertech.com/Excel/Charts/Dynamics.html

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


"@bhi" wrote in message
...


Submitted via EggHeadCafe - Software Developer Portal of Choice
BOOK REVIEW: Silverlight 2 Unleashed / Bugnion [SAMS]
http://www.eggheadcafe.com/tutorials...lverlight.aspx

Jon,

In your example you said that:

=IF(<condition=0,NA(),<formula)

will cause Excel to interpolate a line across where the chart gap would have been.

This is good functionality, but if I want to have Excel instead actually leave the gap between two markers and NOT interpolate the line, how do I do this. In other words I would like the line chart data series to appear disjointed. The chart series is calculated so there is no problem incorporating formulae such as the above.

Also if you would not mind a tangential question, with Excel 2007 is there any way to force an added trend line to appear on the chart visually "behind" the series it is trending, sort of like a z-order or z-index in programming? The default seems to be that it appears on top of the series line and for my use it would be cleaner to have it appear behind instead.

Thanks in advance!

- Daniel Ferry



Jon Peltier wrote:

"" isn't a blank, as you're figuring out.
01-Jul-08

"" isn't a blank, as you're figuring out. It's text, which Excel interprets
as zero. You can't make a chart show a gap in a cell that isn't blank, but
if you use NA() instead of "",

=IF(<condition=0,NA(),<formula)

The cell gets a big ugly #N/A error, which is not plotted in a line or XY
chart. If the chart has markers connected with lines, there will be no
marker where there is #N/A, and the line will be interpolated across where
the gap would have been.

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


"Brandon" <crimson"underscore"m"at"hotmail.com wrote in message
...

Previous Posts In This Thread:

On Tuesday, July 01, 2008 10:11 PM
crimson"underscore"m"at"hotmail.com wrote:

Chart displaying "blank" cells?
In Excel 2007, I would like my chart to not display chart data for empty cells.

On the "Hidden and Empty Cell Settings" I have "Show empty cells as: Gaps".
I can't seem to get this to work, but my cells aren't empty perse. They
return a formula value:

=IF(<condition=0,"",<formula)

The cells that are being displayed (as 0) on the chart appear blank in the
spreadsheet. Any ideas?

On Tuesday, July 01, 2008 11:49 PM
Jon Peltier wrote:

"" isn't a blank, as you're figuring out.
"" isn't a blank, as you're figuring out. It's text, which Excel interprets
as zero. You can't make a chart show a gap in a cell that isn't blank, but
if you use NA() instead of "",

=IF(<condition=0,NA(),<formula)

The cell gets a big ugly #N/A error, which is not plotted in a line or XY
chart. If the chart has markers connected with lines, there will be no
marker where there is #N/A, and the line will be interpolated across where
the gap would have been.

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


"Brandon" <crimson"underscore"m"at"hotmail.com wrote in message
...

On Wednesday, July 02, 2008 3:04 AM
Dave Curtis wrote:

Chart displaying "blank" cells?
Hi,

Use NA() instead of "" which will return a #N'A instead of a empty string.
Your chart will not plot the #N/A

Dave

url:http://www.ureader.com/msg/10296332.aspx

On Friday, July 11, 2008 11:45 AM
wrote:

Chart displaying "blank" cells?
I have the same problem. I tried using NA() but it displayts #NA as a column
in chart. Any suggestion ?

On Saturday, July 12, 2008 11:16 AM
Jon Peltier wrote:

NA() works for line and XY charts. Try "" for column or bar charts.
NA() works for line and XY charts. Try "" for column or bar charts.

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


"@bhi" wrote in message
...

On Saturday, July 12, 2008 12:29 PM
wrote:

I tried all 3(1) NA() i.e #NA(2) ""(3) 0but it creates bar for all of them.
I tried all 3
(1) NA() i.e #NA
(2) ""
(3) 0

but it creates bar for all of them.


"Jon Peltier" wrote:

On Saturday, July 12, 2008 12:53 PM
Jon Peltier wrote:

It creates a bar, or it creates a space where the bar would go?
It creates a bar, or it creates a space where the bar would go?

Maybe you should paste your data into a reply.

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


"@bhi" wrote in message
...

On Monday, July 14, 2008 8:19 PM
bh wrote:

Yes it creates space where bar would go for all NA() (#NA) or 0.
Yes it creates space where bar would go for all NA() (#NA) or 0.
For example I am using similar data same as bellow format
(both year and sales will come on runtime but will not be more than 10 values)

Year Sales
2001 100
2002 200
2003 300
2004 400
2005 500

so here years and its data can come from 2001 to 2010 depend upon user
selected criteria. I want to draw chart only for the which value exist.




I am using

"Jon Peltier" wrote:

On Tuesday, July 15, 2008 7:06 AM
Jon Peltier wrote:

Are the unwanted cells only at the end of the range?
Are the unwanted cells only at the end of the range? Then define a named
range that is as long as the number of rows with data.

http://peltiertech.com/WordPress/200...ynamic-charts/
http://peltiertech.com/Excel/Charts/Dynamics.html

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


"@bhi" wrote in message
...

On Tuesday, December 01, 2009 11:34 PM
daniel ferry wrote:

Charting Question
Jon,

In your example you said that:

=IF(<condition=0,NA(),<formula)

will cause Excel to interpolate a line across where the chart gap would have been.

This is good functionality, but if I want to have Excel instead actually leave the gap between two markers and NOT interpolate the line, how do I do this. In other words I would like the line chart data series to appear disjointed. The chart series is calculated so there is no problem incorporating formulae such as the above.

Also if you would not mind a tangential question, with Excel 2007 is there any way to force an added trend line to appear on the chart visually "behind" the series it is trending, sort of like a z-order or z-index in programming? The default seems to be that it appears on top of the series line and for my use it would be cleaner to have it appear behind instead.

Thanks in advance!

- Daniel Ferry


Submitted via EggHeadCafe - Software Developer Portal of Choice
SQL Server - Detach / Attach / Full Text Catalog
http://www.eggheadcafe.com/tutorials...tach--att.aspx

Hi,

You might be able to mask the line depending upon your data.
http://www.andypope.info/charts/brokenlines.htm

Otherwise code to clear the cell is the only other option.

You can not change the z-order of series and trendlines. One possible way
would be to calculate the trend line using formula an plot that as a
standard series. You can then alter the plot order of the 2 series. This
pages should provide the information you need on using formula.
http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm
http://www.tushar-mehta.com/publish_...nalysis/16.htm

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"daniel ferry" wrote in message ...
Jon,

In your example you said that:

=IF(<condition=0,NA(),<formula)

will cause Excel to interpolate a line across where the chart gap would
have been.

This is good functionality, but if I want to have Excel instead actually
leave the gap between two markers and NOT interpolate the line, how do I
do this. In other words I would like the line chart data series to appear
disjointed. The chart series is calculated so there is no problem
incorporating formulae such as the above.

Also if you would not mind a tangential question, with Excel 2007 is there
any way to force an added trend line to appear on the chart visually
"behind" the series it is trending, sort of like a z-order or z-index in
programming? The default seems to be that it appears on top of the series
line and for my use it would be cleaner to have it appear behind instead.

Thanks in advance!

- Daniel Ferry



Jon Peltier wrote:

"" isn't a blank, as you're figuring out.
01-Jul-08

"" isn't a blank, as you're figuring out. It's text, which Excel
interprets
as zero. You can't make a chart show a gap in a cell that isn't blank, but
if you use NA() instead of "",

=IF(<condition=0,NA(),<formula)

The cell gets a big ugly #N/A error, which is not plotted in a line or XY
chart. If the chart has markers connected with lines, there will be no
marker where there is #N/A, and the line will be interpolated across where
the gap would have been.

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


"Brandon" <crimson"underscore"m"at"hotmail.com wrote in message
...

Previous Posts In This Thread:

On Tuesday, July 01, 2008 10:11 PM
crimson"underscore"m"at"hotmail.com wrote:

Chart displaying "blank" cells?
In Excel 2007, I would like my chart to not display chart data for empty
cells.

On the "Hidden and Empty Cell Settings" I have "Show empty cells as:
Gaps".
I can't seem to get this to work, but my cells aren't empty perse. They
return a formula value:

=IF(<condition=0,"",<formula)

The cells that are being displayed (as 0) on the chart appear blank in the
spreadsheet. Any ideas?

On Tuesday, July 01, 2008 11:49 PM
Jon Peltier wrote:

"" isn't a blank, as you're figuring out.
"" isn't a blank, as you're figuring out. It's text, which Excel
interprets
as zero. You can't make a chart show a gap in a cell that isn't blank, but
if you use NA() instead of "",

=IF(<condition=0,NA(),<formula)

The cell gets a big ugly #N/A error, which is not plotted in a line or XY
chart. If the chart has markers connected with lines, there will be no
marker where there is #N/A, and the line will be interpolated across where
the gap would have been.

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


"Brandon" <crimson"underscore"m"at"hotmail.com wrote in message
...

On Wednesday, July 02, 2008 3:04 AM
Dave Curtis wrote:

Chart displaying "blank" cells?
Hi,

Use NA() instead of "" which will return a #N'A instead of a empty string.
Your chart will not plot the #N/A

Dave

url:http://www.ureader.com/msg/10296332.aspx

On Friday, July 11, 2008 11:45 AM
wrote:

Chart displaying "blank" cells?
I have the same problem. I tried using NA() but it displayts #NA as a
column
in chart. Any suggestion ?

On Saturday, July 12, 2008 11:16 AM
Jon Peltier wrote:

NA() works for line and XY charts. Try "" for column or bar charts.
NA() works for line and XY charts. Try "" for column or bar charts.

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


"@bhi" wrote in message
...

On Saturday, July 12, 2008 12:29 PM
wrote:

I tried all 3(1) NA() i.e #NA(2) ""(3) 0but it creates bar for all of
them.
I tried all 3
(1) NA() i.e #NA
(2) ""
(3) 0

but it creates bar for all of them.


"Jon Peltier" wrote:

On Saturday, July 12, 2008 12:53 PM
Jon Peltier wrote:

It creates a bar, or it creates a space where the bar would go?
It creates a bar, or it creates a space where the bar would go?

Maybe you should paste your data into a reply.

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


"@bhi" wrote in message
...

On Monday, July 14, 2008 8:19 PM
bh wrote:

Yes it creates space where bar would go for all NA() (#NA) or 0.
Yes it creates space where bar would go for all NA() (#NA) or 0.
For example I am using similar data same as bellow format
(both year and sales will come on runtime but will not be more than 10
values)

Year Sales
2001 100
2002 200
2003 300
2004 400
2005 500

so here years and its data can come from 2001 to 2010 depend upon user
selected criteria. I want to draw chart only for the which value exist.




I am using

"Jon Peltier" wrote:

On Tuesday, July 15, 2008 7:06 AM
Jon Peltier wrote:

Are the unwanted cells only at the end of the range?
Are the unwanted cells only at the end of the range? Then define a named
range that is as long as the number of rows with data.

http://peltiertech.com/WordPress/200...ynamic-charts/
http://peltiertech.com/Excel/Charts/Dynamics.html

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


"@bhi" wrote in message
...


Submitted via EggHeadCafe - Software Developer Portal of Choice
BOOK REVIEW: Silverlight 2 Unleashed / Bugnion [SAMS]
http://www.eggheadcafe.com/tutorials...lverlight.aspx

There are tricks to plot a gap instead of interpolating a line. Andy
Pope has an example on his site (http://andypope.info) where a white
line obscures the interpolated line. Another approach is to actually
delete the contents of the cell. All of these tricks have their drawbacks.

The trendline is always plotted in front of the data in a chart. It
would be nice to have more control over the Z order of chart elements.

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



daniel ferry wrote:
Jon,

In your example you said that:

=IF(<condition=0,NA(),<formula)

will cause Excel to interpolate a line across where the chart gap would have been.

This is good functionality, but if I want to have Excel instead actually leave the gap between two markers and NOT interpolate the line, how do I do this. In other words I would like the line chart data series to appear disjointed. The chart series is calculated so there is no problem incorporating formulae such as the above.

Also if you would not mind a tangential question, with Excel 2007 is there any way to force an added trend line to appear on the chart visually "behind" the series it is trending, sort of like a z-order or z-index in programming? The default seems to be that it appears on top of the series line and for my use it would be cleaner to have it appear behind instead.

Thanks in advance!

- Daniel Ferry



Jon Peltier wrote:

"" isn't a blank, as you're figuring out.
01-Jul-08

"" isn't a blank, as you're figuring out. It's text, which Excel interprets
as zero. You can't make a chart show a gap in a cell that isn't blank, but
if you use NA() instead of "",

=IF(<condition=0,NA(),<formula)

The cell gets a big ugly #N/A error, which is not plotted in a line or XY
chart. If the chart has markers connected with lines, there will be no
marker where there is #N/A, and the line will be interpolated across where
the gap would have been.

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


"Brandon" <crimson"underscore"m"at"hotmail.com wrote in message
...

Previous Posts In This Thread:

On Tuesday, July 01, 2008 10:11 PM
crimson"underscore"m"at"hotmail.com wrote:

Chart displaying "blank" cells?
In Excel 2007, I would like my chart to not display chart data for empty cells.

On the "Hidden and Empty Cell Settings" I have "Show empty cells as: Gaps".
I can't seem to get this to work, but my cells aren't empty perse. They
return a formula value:

=IF(<condition=0,"",<formula)

The cells that are being displayed (as 0) on the chart appear blank in the
spreadsheet. Any ideas?

On Tuesday, July 01, 2008 11:49 PM
Jon Peltier wrote:

"" isn't a blank, as you're figuring out.
"" isn't a blank, as you're figuring out. It's text, which Excel interprets
as zero. You can't make a chart show a gap in a cell that isn't blank, but
if you use NA() instead of "",

=IF(<condition=0,NA(),<formula)

The cell gets a big ugly #N/A error, which is not plotted in a line or XY
chart. If the chart has markers connected with lines, there will be no
marker where there is #N/A, and the line will be interpolated across where
the gap would have been.

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


"Brandon" <crimson"underscore"m"at"hotmail.com wrote in message
...

On Wednesday, July 02, 2008 3:04 AM
Dave Curtis wrote:

Chart displaying "blank" cells?
Hi,

Use NA() instead of "" which will return a #N'A instead of a empty string.
Your chart will not plot the #N/A

Dave

url:http://www.ureader.com/msg/10296332.aspx

On Friday, July 11, 2008 11:45 AM
wrote:

Chart displaying "blank" cells?
I have the same problem. I tried using NA() but it displayts #NA as a column
in chart. Any suggestion ?

On Saturday, July 12, 2008 11:16 AM
Jon Peltier wrote:

NA() works for line and XY charts. Try "" for column or bar charts.
NA() works for line and XY charts. Try "" for column or bar charts.

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


"@bhi" wrote in message
...

On Saturday, July 12, 2008 12:29 PM
wrote:

I tried all 3(1) NA() i.e #NA(2) ""(3) 0but it creates bar for all of them.
I tried all 3
(1) NA() i.e #NA
(2) ""
(3) 0

but it creates bar for all of them.


"Jon Peltier" wrote:

On Saturday, July 12, 2008 12:53 PM
Jon Peltier wrote:

It creates a bar, or it creates a space where the bar would go?
It creates a bar, or it creates a space where the bar would go?

Maybe you should paste your data into a reply.

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


"@bhi" wrote in message
...

On Monday, July 14, 2008 8:19 PM
bh wrote:

Yes it creates space where bar would go for all NA() (#NA) or 0.
Yes it creates space where bar would go for all NA() (#NA) or 0.
For example I am using similar data same as bellow format
(both year and sales will come on runtime but will not be more than 10 values)

Year Sales
2001 100
2002 200
2003 300
2004 400
2005 500

so here years and its data can come from 2001 to 2010 depend upon user
selected criteria. I want to draw chart only for the which value exist.




I am using

"Jon Peltier" wrote:

On Tuesday, July 15, 2008 7:06 AM
Jon Peltier wrote:

Are the unwanted cells only at the end of the range?
Are the unwanted cells only at the end of the range? Then define a named
range that is as long as the number of rows with data.

http://peltiertech.com/WordPress/200...ynamic-charts/
http://peltiertech.com/Excel/Charts/Dynamics.html

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


"@bhi" wrote in message
...

On Tuesday, December 01, 2009 11:34 PM
daniel ferry wrote:

Charting Question
Jon,

In your example you said that:

=IF(<condition=0,NA(),<formula)

will cause Excel to interpolate a line across where the chart gap would have been.

This is good functionality, but if I want to have Excel instead actually leave the gap between two markers and NOT interpolate the line, how do I do this. In other words I would like the line chart data series to appear disjointed. The chart series is calculated so there is no problem incorporating formulae such as the above.

Also if you would not mind a tangential question, with Excel 2007 is there any way to force an added trend line to appear on the chart visually "behind" the series it is trending, sort of like a z-order or z-index in programming? The default seems to be that it appears on top of the series line and for my use it would be cleaner to have it appear behind instead.

Thanks in advance!

- Daniel Ferry


Submitted via EggHeadCafe - Software Developer Portal of Choice
SQL Server - Detach / Attach / Full Text Catalog
http://www.eggheadcafe.com/tutorials...tach--att.aspx

I have prepared my data and used =NA() for the empty cells but my line chart still insists on graphing zeros. When I go into "Select Data", "Hidden and Empty Cells", the zero is checked and the other two options are greyed out so that I cannot check either one. I do not want the zeros to be graphed and don't know how to make them go away! Please help!

cindy



Jon Peltier wrote:

"" isn't a blank, as you're figuring out.
01-Jul-08

"" isn't a blank, as you're figuring out. It's text, which Excel interprets
as zero. You can't make a chart show a gap in a cell that isn't blank, but
if you use NA() instead of "",

=IF(<condition=0,NA(),<formula)

The cell gets a big ugly #N/A error, which is not plotted in a line or XY
chart. If the chart has markers connected with lines, there will be no
marker where there is #N/A, and the line will be interpolated across where
the gap would have been.

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


"Brandon" <crimson"underscore"m"at"hotmail.com wrote in message
...

Previous Posts In This Thread:

On Tuesday, July 01, 2008 10:11 PM
crimson"underscore"m"at"hotmail.com wrote:

Chart displaying "blank" cells?
In Excel 2007, I would like my chart to not display chart data for empty cells.

On the "Hidden and Empty Cell Settings" I have "Show empty cells as: Gaps".
I can't seem to get this to work, but my cells aren't empty perse. They
return a formula value:

=IF(<condition=0,"",<formula)

The cells that are being displayed (as 0) on the chart appear blank in the
spreadsheet. Any ideas?

On Tuesday, July 01, 2008 11:49 PM
Jon Peltier wrote:

"" isn't a blank, as you're figuring out.
"" isn't a blank, as you're figuring out. It's text, which Excel interprets
as zero. You can't make a chart show a gap in a cell that isn't blank, but
if you use NA() instead of "",

=IF(<condition=0,NA(),<formula)

The cell gets a big ugly #N/A error, which is not plotted in a line or XY
chart. If the chart has markers connected with lines, there will be no
marker where there is #N/A, and the line will be interpolated across where
the gap would have been.

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


"Brandon" <crimson"underscore"m"at"hotmail.com wrote in message
...

On Wednesday, July 02, 2008 3:04 AM
Dave Curtis wrote:

Chart displaying "blank" cells?
Hi,

Use NA() instead of "" which will return a #N'A instead of a empty string.
Your chart will not plot the #N/A

Dave

url:http://www.ureader.com/msg/10296332.aspx

On Friday, July 11, 2008 11:45 AM
wrote:

Chart displaying "blank" cells?
I have the same problem. I tried using NA() but it displayts #NA as a column
in chart. Any suggestion ?

On Saturday, July 12, 2008 11:16 AM
Jon Peltier wrote:

NA() works for line and XY charts. Try "" for column or bar charts.
NA() works for line and XY charts. Try "" for column or bar charts.

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


"@bhi" wrote in message
...

On Saturday, July 12, 2008 12:29 PM
wrote:

I tried all 3(1) NA() i.e #NA(2) ""(3) 0but it creates bar for all of them.
I tried all 3
(1) NA() i.e #NA
(2) ""
(3) 0

but it creates bar for all of them.


"Jon Peltier" wrote:

On Saturday, July 12, 2008 12:53 PM
Jon Peltier wrote:

It creates a bar, or it creates a space where the bar would go?
It creates a bar, or it creates a space where the bar would go?

Maybe you should paste your data into a reply.

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


"@bhi" wrote in message
...

On Monday, July 14, 2008 8:19 PM
bh wrote:

Yes it creates space where bar would go for all NA() (#NA) or 0.
Yes it creates space where bar would go for all NA() (#NA) or 0.
For example I am using similar data same as bellow format
(both year and sales will come on runtime but will not be more than 10 values)

Year Sales
2001 100
2002 200
2003 300
2004 400
2005 500

so here years and its data can come from 2001 to 2010 depend upon user
selected criteria. I want to draw chart only for the which value exist.




I am using

"Jon Peltier" wrote:

On Tuesday, July 15, 2008 7:06 AM
Jon Peltier wrote:

Are the unwanted cells only at the end of the range?
Are the unwanted cells only at the end of the range? Then define a named
range that is as long as the number of rows with data.

http://peltiertech.com/WordPress/200...ynamic-charts/
http://peltiertech.com/Excel/Charts/Dynamics.html

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


"@bhi" wrote in message
...

On Tuesday, December 01, 2009 11:34 PM
daniel ferry wrote:

Charting Question
Jon,

In your example you said that:

=IF(<condition=0,NA(),<formula)

will cause Excel to interpolate a line across where the chart gap would have been.

This is good functionality, but if I want to have Excel instead actually leave the gap between two markers and NOT interpolate the line, how do I do this. In other words I would like the line chart data series to appear disjointed. The chart series is calculated so there is no problem incorporating formulae such as the above.

Also if you would not mind a tangential question, with Excel 2007 is there any way to force an added trend line to appear on the chart visually "behind" the series it is trending, sort of like a z-order or z-index in programming? The default seems to be that it appears on top of the series line and for my use it would be cleaner to have it appear behind instead.

Thanks in advance!

- Daniel Ferry

On Tuesday, December 01, 2009 11:37 PM
daniel ferry wrote:

Chart Blanks
Jon,

In your example you said that:

=IF(<condition=0,NA(),<formula)

will cause Excel to interpolate a line across where the chart gap would have been.

This is good functionality, but if I want to have Excel instead actually leave the gap between two markers and NOT interpolate the line, how do I do this. In other words I would like the line chart data series to appear disjointed. The chart series is calculated so there is no problem incorporating formulae such as the above.

Also if you would not mind a tangential question, with Excel 2007 is there any way to force an added trend line to appear on the chart visually "behind" the series it is trending, sort of like a z-order or z-index in programming? The default seems to be that it appears on top of the series line and for my use it would be cleaner to have it appear behind instead.

Thanks in advance!

- Daniel Ferry


Submitted via EggHeadCafe - Software Developer Portal of Choice
Dlink / Linksys Bluetooth USB Adapter Blues
http://www.eggheadcafe.com/tutorials...bluetooth.aspx

I had typed in the month-year for a report I was working on (ie. Jan-10) and found that on one sheet I was able to extend the "graph range" and on another one I wasn't able to. What I did was change the data type from what I would call static content in the cell to an actual formatted date value. So in your case if it were year that you were looking at you would put something like 1/1/2009 for 2009 and 1/1/2010 for 2010 and then format the cell as a custom format to yyyy which should only show the year. The chart should use the field as a date instead of an integer and not show it. I hope I'm explaining this properly, please let me know!



Cindy Seal wrote:

Excel Hidden and empty cells
11-Jan-10

I have prepared my data and used =NA() for the empty cells but my line chart still insists on graphing zeros. When I go into "Select Data", "Hidden and Empty Cells", the zero is checked and the other two options are greyed out so that I cannot check either one. I do not want the zeros to be graphed and don't know how to make them go away! Please help!

cindy

Previous Posts In This Thread:

On Tuesday, July 01, 2008 10:11 PM
crimson"underscore"m"at"hotmail.com wrote:

Chart displaying "blank" cells?
In Excel 2007, I would like my chart to not display chart data for empty cells.

On the "Hidden and Empty Cell Settings" I have "Show empty cells as: Gaps".
I can't seem to get this to work, but my cells aren't empty perse. They
return a formula value:

=IF(<condition=0,"",<formula)

The cells that are being displayed (as 0) on the chart appear blank in the
spreadsheet. Any ideas?

On Tuesday, July 01, 2008 11:49 PM
Jon Peltier wrote:

"" isn't a blank, as you're figuring out.
"" isn't a blank, as you're figuring out. It's text, which Excel interprets
as zero. You can't make a chart show a gap in a cell that isn't blank, but
if you use NA() instead of "",

=IF(<condition=0,NA(),<formula)

The cell gets a big ugly #N/A error, which is not plotted in a line or XY
chart. If the chart has markers connected with lines, there will be no
marker where there is #N/A, and the line will be interpolated across where
the gap would have been.

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


"Brandon" <crimson"underscore"m"at"hotmail.com wrote in message
...

On Wednesday, July 02, 2008 3:04 AM
Dave Curtis wrote:

Chart displaying "blank" cells?
Hi,

Use NA() instead of "" which will return a #N'A instead of a empty string.
Your chart will not plot the #N/A

Dave

url:http://www.ureader.com/msg/10296332.aspx

On Friday, July 11, 2008 11:45 AM
wrote:

Chart displaying "blank" cells?
I have the same problem. I tried using NA() but it displayts #NA as a column
in chart. Any suggestion ?

On Saturday, July 12, 2008 11:16 AM
Jon Peltier wrote:

NA() works for line and XY charts. Try "" for column or bar charts.
NA() works for line and XY charts. Try "" for column or bar charts.

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


"@bhi" wrote in message
...

On Saturday, July 12, 2008 12:29 PM
wrote:

I tried all 3(1) NA() i.e #NA(2) ""(3) 0but it creates bar for all of them.
I tried all 3
(1) NA() i.e #NA
(2) ""
(3) 0

but it creates bar for all of them.


"Jon Peltier" wrote:

On Saturday, July 12, 2008 12:53 PM
Jon Peltier wrote:

It creates a bar, or it creates a space where the bar would go?
It creates a bar, or it creates a space where the bar would go?

Maybe you should paste your data into a reply.

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


"@bhi" wrote in message
...

On Monday, July 14, 2008 8:19 PM
bh wrote:

Yes it creates space where bar would go for all NA() (#NA) or 0.
Yes it creates space where bar would go for all NA() (#NA) or 0.
For example I am using similar data same as bellow format
(both year and sales will come on runtime but will not be more than 10 values)

Year Sales
2001 100
2002 200
2003 300
2004 400
2005 500

so here years and its data can come from 2001 to 2010 depend upon user
selected criteria. I want to draw chart only for the which value exist.




I am using

"Jon Peltier" wrote:

On Tuesday, July 15, 2008 7:06 AM
Jon Peltier wrote:

Are the unwanted cells only at the end of the range?
Are the unwanted cells only at the end of the range? Then define a named
range that is as long as the number of rows with data.

http://peltiertech.com/WordPress/200...ynamic-charts/
http://peltiertech.com/Excel/Charts/Dynamics.html

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


"@bhi" wrote in message
...

On Tuesday, December 01, 2009 11:34 PM
daniel ferry wrote:

Charting Question
Jon,

In your example you said that:

=IF(<condition=0,NA(),<formula)

will cause Excel to interpolate a line across where the chart gap would have been.

This is good functionality, but if I want to have Excel instead actually leave the gap between two markers and NOT interpolate the line, how do I do this. In other words I would like the line chart data series to appear disjointed. The chart series is calculated so there is no problem incorporating formulae such as the above.

Also if you would not mind a tangential question, with Excel 2007 is there any way to force an added trend line to appear on the chart visually "behind" the series it is trending, sort of like a z-order or z-index in programming? The default seems to be that it appears on top of the series line and for my use it would be cleaner to have it appear behind instead.

Thanks in advance!

- Daniel Ferry

On Tuesday, December 01, 2009 11:37 PM
daniel ferry wrote:

Chart Blanks
Jon,

In your example you said that:

=IF(<condition=0,NA(),<formula)

will cause Excel to interpolate a line across where the chart gap would have been.

This is good functionality, but if I want to have Excel instead actually leave the gap between two markers and NOT interpolate the line, how do I do this. In other words I would like the line chart data series to appear disjointed. The chart series is calculated so there is no problem incorporating formulae such as the above.

Also if you would not mind a tangential question, with Excel 2007 is there any way to force an added trend line to appear on the chart visually "behind" the series it is trending, sort of like a z-order or z-index in programming? The default seems to be that it appears on top of the series line and for my use it would be cleaner to have it appear behind instead.

Thanks in advance!

- Daniel Ferry

On Monday, January 11, 2010 1:37 PM
Cindy Seal wrote:

Excel Hidden and empty cells
I have prepared my data and used =NA() for the empty cells but my line chart still insists on graphing zeros. When I go into "Select Data", "Hidden and Empty Cells", the zero is checked and the other two options are greyed out so that I cannot check either one. I do not want the zeros to be graphed and don't know how to make them go away! Please help!

cindy


Submitted via EggHeadCafe - Software Developer Portal of Choice
Build a Selected Text Favorites Utility for your Web Site
http://www.eggheadcafe.com/tutorials...-text-fav.aspx