Graphing with "Empty" vs "Blank" Cells

[WebColin]

I am trying to create a line graph based on a table, where each cell within
that table has its value set by a condition. If there is not enough data, I
want a gap in the graph for that time period. Instead, I am getting 0 values
on the graph.

I currently have the condition set the cell value to "" if there is
insufficient data.

It's sort of like this (actual formula is much longer, but concept is the
same): =if(X1>mindatacount,Y1,"")

Where "mindatacount" is just the minimum number of data points, tracked in
row X.

And when I graph it, I want the graph to ignore cells that are blank (hence
the "" if the count is too small). But because they are not "Empty" it
treats them as Zero in the graph.

Is there a workaround? Is there any way in my condition to define the cell
as Empty if there isn't sufficient data or to make the graph treat Blank
cells the same as Empty cells?

Thanks for any help,
Colin

 

[Andy Pope]

Hi,

There is no way to automatically get broken lines when a cell is not
empty. For line charts you can use NA() instead of "" to remove the data
marker but the line will be interpolated between valid points.

=if(X1>mindatacount,Y1,NA())

Depending on your data you may be able to use this approach to mask the
line.
http://www.andypope.info/charts/brokenlines.htm

Cheers
Andy

 

[Shane Devenshire]

Hi,

You can write VBA code to display line as the same color as the background
and the Marker as None for the desired data points.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire