How to End Chart Line If Data Source Has a Blank?

  • Thread starter Thread starter evoxfan
  • Start date Start date
E

evoxfan

I have a simple line chart with a data source in the same file on the
previous worksheet labeled DATA. (Chart worksheet is labeled CHART.)

Heres is a couple of my lines from the DATA source:

Assume columns are A - H.
11/23/08 107 3,607 287 4,500 13 3735.5 893
11/30/08 370 3,977 280 4,737 12 3365.5 760
12/7/08 280 4,974 11
12/14/08 280 5,210 10

Columns A, D, E, & F are all preset data. Column B data is entered weekly
and all the other columns contain formulas that generate data based on Column
B's entry.

So that is what I have, and now my one problem is with Column C and here is
the formula in Column C cells:
C26 =IF(ISNUMBER($B26),C25+B26," ")
C27 =IF(ISNUMBER($B27),C26+B27," ") and so on to C37 where the data ends.

There is no issue when data is entered into Column B, only with the cells
that have blanks (" ") from the formula. The chart counts these blanks as
zero so it takes a nose dive from the last weekly entry cell data (currently
3,937) to zero and goes to the end of the chart at zero.

I want the chat line to end at the last cell with data and ignore the rest
of the blanks so I do not have a nose dive on my chart.

What is the best way to accomplish this?
Perhaps a simple formula change in the data cells?

Thanks in advance for any help b/c I am lost.
 
Hi,

Use NA() instead of " ", as that will be treated as text and text in a chart
is treated as zero.

C26 =IF(ISNUMBER($B26),C25+B26,NA())

Cheers
Andy
 
Back
Top