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" wrote:

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." wrote in message
...
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" wrote in message
...
Hi,

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

Cheers
Andy

Bill H. wrote:
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" wrote in message
...

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, Microsoft MVP - Excel
http://www.andypope.info
"Bill H." wrote in message
.. .

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.

--
Bill





--

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