Do not want chart to graph cells with formulas that are "blank"

[Guest]

I am trying to chart % actual v. % plan data.
I want the chart to show % plan data through 6 quarters
I want the chart to show only % actual through current quarter

- I have table1 that will get updated monthly with values representing
"actual" data.
- I have table2 that is filled with formulas that gets autopopulated with %
data based on values entered in table1.
- Table2 formula is something like this [If(table1C6="","",1-table1C6/C14)].
That way the cells in table2 look blank until values are entered in table1.
- I have chart1 that graphs the output of table2 (along with "% plan" data
from table3).
- chart1 graphs the blank cells in table2 as "0" apparently because the
cells are not truly "blank", i.e. the cells have the
[If(table1C6="","",1-table1C6/C14)] formula.
- with my curser activating chart1, I choose Tools-->Options-->Chart-->Plot
Empty Cells as 'not plotted' (leave gaps)
- it still charts the "empty cells" as 0

Goal: to have chart1 plot the data from table2 as table1 data is entered
each month. Do not plot the "blank" cells from table2 as "0". And I don't
want to have to remove the formulas from table2, otherwise I'll have to
update both table1 and table2 every month (the point of having formulas).

thanks for any thoughts on how to accomplish this!!

 

[Jon Peltier]

What kind of chart is it? This technique works with XY and Line charts.

Note that "" in a cell means the cell is not blank, it includes the
string "". Change your formula to:

If(table1C6="",NA(),1-table1C6/C14)

NA() produces the #N/A error in the worksheet, which looks ugly, but is
not plotted in the chart. You don't get a gap in the connecting line,
instead you get the line interpolated over the gap, connecting the
points on either side.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______