Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 97 - Graph not to show Zeros

    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?

  2. #2
    New Lounger
    Join Date
    Apr 2001
    Location
    Honolulu, HI
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97 - Graph not to show Zeros

    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

  3. #3
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97 - Graph not to show Zeros

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •