linear charting with zeros

  • Thread starter Thread starter Bill H.
  • Start date Start date
B

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.
 
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
 
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

Andy Pope said:
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
Click to expand...
 
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
Click to expand...
 
I also discovered that formulas on the columns containing the #N/A now don't
work. For example MAX returns #N/A.

sheesh!

--Bill

Andy Pope said:
Hi,

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

Cheers
Andy
Click to expand...
 
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
Bill H. said:
I also discovered that formulas on the columns containing the #N/A now
don't work. For example MAX returns #N/A.

sheesh!

--Bill
Click to expand...
 
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.

Andy Pope said:
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
Click to expand...
 
Back
Top