A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Blank instead of ""



 
 
Thread Tools Display Modes
  #1  
Old March 29th, 2010, 11:43 AM posted to microsoft.public.excel.charting
LiAD
external usenet poster
 
Posts: 194
Default 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
  #2  
Old March 29th, 2010, 01:22 PM posted to microsoft.public.excel.charting
Bernard Liengme
external usenet poster
 
Posts: 516
Default 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


  #3  
Old March 29th, 2010, 03:57 PM posted to microsoft.public.excel.charting
LiAD
external usenet poster
 
Posts: 194
Default 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


.

  #4  
Old March 29th, 2010, 05:21 PM posted to microsoft.public.excel.charting
Luke M[_4_]
external usenet poster
 
Posts: 451
Default 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


.



  #5  
Old March 30th, 2010, 07:41 AM posted to microsoft.public.excel.charting
LiAD
external usenet poster
 
Posts: 194
Default 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

.



.

  #6  
Old March 30th, 2010, 11:51 PM posted to microsoft.public.excel.charting
Jon Peltier[_2_]
external usenet poster
 
Posts: 386
Default 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

.



.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:34 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.