Excel 97 – Graph not to show Zeros

Topic

New Reply

Okay, here’s on for you. You have an Excel graph, with a totals line (Column C) based on a formula that will automatically calculate when you enter the months totals in columns A & B. You want the formula in column c to be entered thru the end of the year, but you do not want your graph the spike to zero where no data is yet entered. I say it just lazy not to drag you formula down, but none the less, is there some sort of “if” statement that says if no data exist, no line on graph?

Reply | Quote

Replies

A line chart series will drop to zero wherever the series data value is 0 or is not numeric (text or error). You could:
1. change the chart style to column
2. manually clear the plotted cells that sum to 0
3. design a macro that adds sum formulas in Column C only as you enter data in A:B

Good luck,

John

Reply | Quote

the way I handle this is as follows: =IF(AND(ISBLANK(A1),ISBLANK(B1)),#n/a,A1+B1) (entered in c1 where your formula before was =a1+b1). This has the unlooked for advantage that it will also interpolate between two points if for instance you don’t have data for march but do for february and april. In this case, although you are putting an error value into the series, excel can handle it.

Reply | Quote