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?

"" 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
news2259E65-5AFA-427B-A5F6-1CAD540BD595@microsoft.com...
> 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?

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" <@bhi@discussions.microsoft.com> wrote in message
news:6CD2F060-CBD2-473A-900F-0B6C4FAC9229@microsoft.com...
>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" <@bhi@discussions.microsoft.com> wrote in message
> news:6CD2F060-CBD2-473A-900F-0B6C4FAC9229@microsoft.com...
> >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" <@bhi@discussions.microsoft.com> wrote in message
news:FD13C4CD-BAA4-418B-95A1-7388EAA9B307@microsoft.com...
>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" <@bhi@discussions.microsoft.com> wrote in message
>> news:6CD2F060-CBD2-473A-900F-0B6C4FAC9229@microsoft.com...
>> >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" <@bhi@discussions.microsoft.com> wrote in message
> news:FD13C4CD-BAA4-418B-95A1-7388EAA9B307@microsoft.com...
> >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" <@bhi@discussions.microsoft.com> wrote in message
> >> news:6CD2F060-CBD2-473A-900F-0B6C4FAC9229@microsoft.com...
> >> >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/20...dynamic-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" <bhi@discussions.microsoft.com> wrote in message
news:1697B473-3894-499E-84FF-E02500EE89E1@microsoft.com...
> 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" <@bhi@discussions.microsoft.com> wrote in message
>> news:FD13C4CD-BAA4-418B-95A1-7388EAA9B307@microsoft.com...
>> >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" <@bhi@discussions.microsoft.com> wrote in message
>> >> news:6CD2F060-CBD2-473A-900F-0B6C4FAC9229@microsoft.com...
>> >> >I have the same problem. I tried using NA() but it displayts #NA as a
>> >> >column
>> >> > in chart. Any suggestion ?
>> >>
>> >>
>> >>
>>
>>
>>