Static Range, Dynamic Chart Data

Bob -

This formula won't plot a zero point:

=IF(A1=0,NA(),SUM(A1:A6))

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

airbob wrote:

I have read some great tips on making my chart dynamic that involve
changing the range. The chart I would like to create is a 12 month line
chart. I want the chart to always be 12 months in length, but the point
to simply not be plotted if the value of the cell "blank". In other
words, stop the line short of the end of the plot area.

The 12 months of charted cells have a formula like
=IF(A1=0,"",SUM(A1:A6)). So you see a blank cell if A1 is 0. But the
chart shows this as a zero value. If I delete the contents the chart
does what I would like.

Is there a way to simply change the formula? Is there another way to
make this cell not show in the chart without having to copy or re-enter
the formula when I want it to chart?

Bob


---
Message posted from http://www.ExcelForum.com/

Bob -

Glad it worked. To complete your spreadsheet experience, use conditional
formatting to hide the error. Select the cells, choose Conditional
Formatting from the Format menu, change the Cell Value Is to Formula Is
under Condition 1, enter =ISERROR(A1) in the box (A1 is the active cell
address), and choose a font color that blends into the background.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

airbob wrote:

Jon,

Thank you very much. That is just what I needed.

One minor thing. When using the NA(), is there any way to not print or
display #NA in the cell?

Even so, I can live with the #NA. I am very grateful. Thanks again.

Bob


---
Message posted from http://www.ExcelForum.com/