Blank instead of ""
 

Hi,

In a chart I have a formula that either returns a number or "" in a cell. I
then have a dynamic range set which plots the values on a graph. The problem
is that the chart will plot the ""s as zeros as it sees something in the cell.

Is there a way I can have a true blank cell result coming from a formula?

If not how I can avoid the dynamic range seeing this as a zero?

Thanks
LiAD

Blank instead of ""
 

Replace the blank by #N/A
So let's say your formula is =IF(this-test, B2,"") then use
=IF(this-test,B2,NA())
When the test fails, this displays #N/A which the chart engine ignores
If this looks odd in a print out, use a conditional format such as =ISNA(C2)
and make the font the same as the cell background - make it invisible on the
screen and then printed.
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"LiAD" wrote in message
...
Hi,

In a chart I have a formula that either returns a number or "" in a cell.
I
then have a dynamic range set which plots the values on a graph. The
problem
is that the chart will plot the ""s as zeros as it sees something in the
cell.

Is there a way I can have a true blank cell result coming from a formula?

If not how I can avoid the dynamic range seeing this as a zero?

Thanks
LiAD

Blank instead of ""
 

Hi,

Thanks for the suggestion.

I'm afraid the chart engine (or at least mine anyway) doesn't ignore it - it
plots nothing on the graph but shows a lot of empty space which
increases/decreases as i add/delete N/A's.

If i try this technique on a chart without dynamic ranges it doesnt work
either.

Any ideas why this would be different to your result?

Thanks

"Bernard Liengme" wrote:

Replace the blank by #N/A
So let's say your formula is =IF(this-test, B2,"") then use
=IF(this-test,B2,NA())
When the test fails, this displays #N/A which the chart engine ignores
If this looks odd in a print out, use a conditional format such as =ISNA(C2)
and make the font the same as the cell background - make it invisible on the
screen and then printed.
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"LiAD" wrote in message
...
Hi,

In a chart I have a formula that either returns a number or "" in a cell.
I
then have a dynamic range set which plots the values on a graph. The
problem
is that the chart will plot the ""s as zeros as it sees something in the
cell.

Is there a way I can have a true blank cell result coming from a formula?

If not how I can avoid the dynamic range seeing this as a zero?

Thanks
LiAD

.

Blank instead of ""
 

What type of chart are you using? Jon Peltier provides several examples of
how you can handle gaps, and how different chart types vary at:
http://peltiertech.com/WordPress/min...g-empty-cells/

--
Best Regards,

Luke M
"LiAD" wrote in message
...
Hi,

Thanks for the suggestion.

I'm afraid the chart engine (or at least mine anyway) doesn't ignore it -
it
plots nothing on the graph but shows a lot of empty space which
increases/decreases as i add/delete N/A's.

If i try this technique on a chart without dynamic ranges it doesnt work
either.

Any ideas why this would be different to your result?

Thanks

"Bernard Liengme" wrote:

Replace the blank by #N/A
So let's say your formula is =IF(this-test, B2,"") then use
=IF(this-test,B2,NA())
When the test fails, this displays #N/A which the chart engine ignores
If this looks odd in a print out, use a conditional format such as
=ISNA(C2)
and make the font the same as the cell background - make it invisible on
the
screen and then printed.
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"LiAD" wrote in message
...
Hi,

In a chart I have a formula that either returns a number or "" in a
cell.
I
then have a dynamic range set which plots the values on a graph. The
problem
is that the chart will plot the ""s as zeros as it sees something in
the
cell.

Is there a way I can have a true blank cell result coming from a
formula?

If not how I can avoid the dynamic range seeing this as a zero?

Thanks
LiAD

.

Blank instead of ""
 

Thanks - looing through JPs stuff I can't find a solution - it applies to
blank cells in the middle of data not at the end.

My blanks at the end are necessary as the user selects a product then the
data and graph update automatically. As the amount of data changes I need a
formula that returns either data or "" (or something else that the graph
cannot plot).

I'm using a line chart.

I have a list of data which contains a string of NA#s at the end. When I
plot it the graph doesnt plot the NA# but it shifts the line to the left as
if it was plotted. What I would like is the last point of the graph to be to
the very right of the graph to use all the space. If I replace the NA() with
"" i get the same result. If I delete the cell completely the graph moves to
the right as it should.

I'm using dynamic ranges.

Thanks
LiAD

"Luke M" wrote:

What type of chart are you using? Jon Peltier provides several examples of
how you can handle gaps, and how different chart types vary at:
http://peltiertech.com/WordPress/min...g-empty-cells/

--
Best Regards,

Luke M
"LiAD" wrote in message
...
Hi,

Thanks for the suggestion.

I'm afraid the chart engine (or at least mine anyway) doesn't ignore it -
it
plots nothing on the graph but shows a lot of empty space which
increases/decreases as i add/delete N/A's.

If i try this technique on a chart without dynamic ranges it doesnt work
either.

Any ideas why this would be different to your result?

Thanks

"Bernard Liengme" wrote:

Replace the blank by #N/A
So let's say your formula is =IF(this-test, B2,"") then use
=IF(this-test,B2,NA())
When the test fails, this displays #N/A which the chart engine ignores
If this looks odd in a print out, use a conditional format such as
=ISNA(C2)
and make the font the same as the cell background - make it invisible on
the
screen and then printed.
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"LiAD" wrote in message
...
Hi,

In a chart I have a formula that either returns a number or "" in a
cell.
I
then have a dynamic range set which plots the values on a graph. The
problem
is that the chart will plot the ""s as zeros as it sees something in
the
cell.

Is there a way I can have a true blank cell result coming from a
formula?

If not how I can avoid the dynamic range seeing this as a zero?

Thanks
LiAD

.



.

Blank instead of ""
 

You need to define your chart data dynamically:

Dynamic Charts » Peltier Tech Blog
http://peltiertech.com/WordPress/dynamic-charts/

Dynamic Chart Review » Peltier Tech Blog
http://peltiertech.com/WordPress/dynamic-chart-review/

Dynamic and Interactive Charts
http://peltiertech.com/Excel/Charts/Dynamics.html

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


On 3/30/2010 2:41 AM, LiAD wrote:
Thanks - looing through JPs stuff I can't find a solution - it applies to
blank cells in the middle of data not at the end.

My blanks at the end are necessary as the user selects a product then the
data and graph update automatically. As the amount of data changes I need a
formula that returns either data or "" (or something else that the graph
cannot plot).

I'm using a line chart.

I have a list of data which contains a string of NA#s at the end. When I
plot it the graph doesnt plot the NA# but it shifts the line to the left as
if it was plotted. What I would like is the last point of the graph to be to
the very right of the graph to use all the space. If I replace the NA() with
"" i get the same result. If I delete the cell completely the graph moves to
the right as it should.

I'm using dynamic ranges.

Thanks
LiAD

"Luke M" wrote:

What type of chart are you using? Jon Peltier provides several examples of
how you can handle gaps, and how different chart types vary at:
http://peltiertech.com/WordPress/min...g-empty-cells/

--
Best Regards,

Luke M
wrote in message
...
Hi,

Thanks for the suggestion.

I'm afraid the chart engine (or at least mine anyway) doesn't ignore it -
it
plots nothing on the graph but shows a lot of empty space which
increases/decreases as i add/delete N/A's.

If i try this technique on a chart without dynamic ranges it doesnt work
either.

Any ideas why this would be different to your result?

Thanks

"Bernard Liengme" wrote:

Replace the blank by #N/A
So let's say your formula is =IF(this-test, B2,"") then use
=IF(this-test,B2,NA())
When the test fails, this displays #N/A which the chart engine ignores
If this looks odd in a print out, use a conditional format such as
=ISNA(C2)
and make the font the same as the cell background - make it invisible on
the
screen and then printed.
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

wrote in message
...
Hi,

In a chart I have a formula that either returns a number or "" in a
cell.
I
then have a dynamic range set which plots the values on a graph. The
problem
is that the chart will plot the ""s as zeros as it sees something in
the
cell.

Is there a way I can have a true blank cell result coming from a
formula?

If not how I can avoid the dynamic range seeing this as a zero?

Thanks
LiAD

.



.