Static Range, Dynamic Chart Data

  • Thread starter Thread starter airbob
  • Start date Start date
A

airbob

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

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

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

Bo
 
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 o
display #NA in the cell?

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

Bo
 
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
 
Back
Top