linear charting with zeros

[Bill H.]

Have a line chart covering many cells. Data is added to each cell daily.
The data cells use a formula. Problem is, the formula evaluates to zero
without a value in the formula's source cell. Thus, the line chart, for
future values, goes to zero.

Is there a way to have the line on the chart simply "end" without showing
zero out to the end of the data range?

Thx.

 

[Andy Pope]

Hi,

You need to adjust your formula so it evaluates to #N/A rather than zero.

=IF( <test> , <value> , NA() )

Line charts will not display markers for #N/A and the line is interpolated
between valid points.

Cheers
Andy

 

[Bill H.]

OK, but then the column with the formula displays #N/A instead of (what I
had before) a space.

Can the display of #N/A be set to not show in the cells?

--Bill

Hi,

You need to adjust your formula so it evaluates to #N/A rather than zero.

=IF( <test> , <value> , NA() )

Line charts will not display markers for #N/A and the line is interpolated
between valid points.

Cheers
Andy

 

[Andy Pope]

Hi,

You could use conditional formatting to display the cells contents in
the same colour as the cells fill colour.

Cheers
Andy

OK, but then the column with the formula displays #N/A instead of (what I
had before) a space.

Can the display of #N/A be set to not show in the cells?

--Bill

 

[Bill H.]

I also discovered that formulas on the columns containing the #N/A now don't
work. For example MAX returns #N/A.

sheesh!

--Bill

Hi,

You could use conditional formatting to display the cells contents in the
same colour as the cells fill colour.

Cheers
Andy

 

[Andy Pope]

Can you not just have to sets of the data. One for the charts with the #N/As
The other for presentation and the source for the MIN/MAX formula.

To get around #N/A with the MAX function you would need to add an IF
function and use array formula.

Confirm array formula using CTRL+SHIFT+ENTER

=MAX(IF(ISNA(<range>),0,<range>))

Cheers
Andy
--

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

I also discovered that formulas on the columns containing the #N/A now
don't work. For example MAX returns #N/A.

sheesh!

--Bill

 

[Jim]

How do I make a =SUM formula return NA whne there are no value to SUM yet?

I have the same problem. I want to have a line chart stop in the month where
there is no data yet.

Can you not just have to sets of the data. One for the charts with the #N/As
The other for presentation and the source for the MIN/MAX formula.

To get around #N/A with the MAX function you would need to add an IF
function and use array formula.

Confirm array formula using CTRL+SHIFT+ENTER

=MAX(IF(ISNA(<range>),0,<range>))

Cheers
Andy

 

[Jim]

Thank you!

=IF(COUNT(A1:A99)=0,NA(),SUM(A1:A99))