Don't graph blanks

[Ed Needshelp]

Interesting.

Now it draws a line between the day before's and the day after'
production.

Unfortunately that makes it look like there was production for tha
day.

Is there a way to convince Excel to ignore that cell like it does
empty cell?

Is there anyway to return a value from an "IF" that is equivalent to a
empty cell

 

[routeram]

Hi,

Try returning a "" from the if statement and see if that helps. (Ope
quotes and close immediately). I think it should work.

Regards,
Ra

 

[Jon Peltier]

He already stated that he did that and it was not acceptable.

Unfortunately there is no BLANK() worksheet function you could return to mimic a
blank. NA() is the closest you can come for a chart. You could use markers for the
data points, so a line spanning a gap is not so mistakable for an actual point.
Tushar Mehta has an approach on his web site that allows the line to disappear where
there is NA() data (http://tushar-mehta.com).

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

 

[Tushar Mehta]

As has been pointed out, there is no way to leave a gap without
programmatic support (http://www.tushar-
mehta.com/excel/software/na_discontinuity/index.html). Unfortunately,
because of a bug introduced in a recent version of XL, even that works
only with a XY Scatter chart and not a line chart.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Sean Bricmont]

I am having a similar problem as Ed and was wondering if any of you
had a shortcut or simple programming solution around the graphing of
blanks? I've looked at Tushar's site, but unfortunately I have to use
line charting.

I'm working with a raw DBF file, a middle-man excel file where I
massage the data and format it correctly and a final graphs file. I've
been using if statements on both the middle file and the graphs file,
but, as Ed and I have regrettably discovered, dynamic charts don't
recognize a ="" as a N/A value.

Also, I can't have "#N/A" showing up in all my blank cells because the
data for the particular graphs is also on display directly below the
graphs! I'm using the if statements because i never know how many
periods my DBF file will return (it's always changing).

Is there someway to have a #N/A cell appear blank but maintain the
graphing characteristics of a #N/A cell? Should I be using something
other than if statements?

I really appreciate any help.

Thanks,
Sean B.

 

[Sean Bricmont]

I am having a similar problem as Ed and was wondering if any of you
had a shortcut or simple programming solution around the graphing of
blanks? I've looked at Tushar's site, but unfortunately I have to use
line charting.

I'm working with a raw DBF file, a middle-man excel file where I
massage the data and format it correctly and a final graphs file. I've
been using if statements on both the middle file and the graphs file,
but, as Ed and I have regrettably discovered, dynamic charts don't
recognize a ="" as a N/A value.

Also, I can't have "#N/A" showing up in all my blank cells because the
data for the particular graphs is also on display directly below the
graphs! I'm using the if statements because i never know how many
periods my DBF file will return (it's always changing).

Is there someway to have a #N/A cell appear blank but maintain the
graphing characteristics of a #N/A cell? Should I be using something
other than if statements?

I really appreciate any help.

Thanks,
Sean B.

 

[Jon Peltier]

You can use conditional formatting to hide the error. Deb Dalgleish shows you how:

http://contextures.com/xlCondFormat03.html#Errors

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