I need a gap when the result of a formula is blank or zero

[vsoler]

Hello everyone,

I have a line chart that plots the evolution of an economic indicator
over the months.

Each of the 12 cells plotted (the source range) has a formula. It
fetches a value from a dabase. For all the months to come, the formula
shows "" (like empty cell) or a blank (" "). I might use zero, were it
needed.

But what I need is that beyond the current month, the line makes a gap
(no dots) instead of treating formulas as zeroes.

Are there any parameters or options that could help me?

Thank you
Vicente Soler

 

[Andy Pope]

Hi,

Use NA() instead of zero or "". Charts treat text as zero.

Using NA() will only stop the markers from being plotted. The line is
interpolated between valid points. But as you will have no valid points for
the first NA() to the end of the series the line will not be displayed.

Cheers
Andy

 

[vsoler]

Hi,

Use NA() instead of zero or "". Charts treat text as zero.

Using NA() will only stop the markers from being plotted. The line is
interpolated between valid points. But as you will have no valid points for
the first NA() to the end of the series the line will not be displayed.

Cheers
Andy
--











- Show quoted text -

Thank you Andy,

With NA() or #N/A it really works. However, I would like the source
cells to appear as if they were blanks. I have looked in the help file
for custom formats, and I can customize positive numbers, negative
numbers, zeroes and text, but not error values line #N/A.

Is there any way to show #N/A cells as blanks?

Thank you again.

Vicente Soler

 

[Andy Pope]

Conditional formatting, change cell reference to suit.

Formula is: =ISNA($A$1)

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi,

Use NA() instead of zero or "". Charts treat text as zero.

Using NA() will only stop the markers from being plotted. The line is
interpolated between valid points. But as you will have no valid points
for
the first NA() to the end of the series the line will not be displayed.

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excelhttp://www.andypope.info"vsoler"










- Show quoted text -

Thank you Andy,

With NA() or #N/A it really works. However, I would like the source
cells to appear as if they were blanks. I have looked in the help file
for custom formats, and I can customize positive numbers, negative
numbers, zeroes and text, but not error values line #N/A.

Is there any way to show #N/A cells as blanks?

Thank you again.

Vicente Soler