Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    need line graph to omit plotting zeros

    Hi..using Excel 2010.
    I can not figure out how to have excel not plot a zero in my line graphs. I have a data range that goes to the year 2020, and the values for 2012-2020 are blank, but the lines drop to the zero value at 2012.
    In the "hidden and empty cells" dialog withing the "select data" box, I checked "gaps".
    I also have unchecked on the "show a zero in cells that have zero value".

    the referenced cells that the chart is plotting has a simple addition formula. not sure if that would affect anything.

    I just want the graph to stop at 2011, the last data point not a zero.

    Thanks!
    Rick

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    A small number, though not equal to zero will plot near zero (excel only ignores real zeroes). Blanks are not plott, but most Errors and all text (even null strings!) will plot as zeroes. If the cell has a formula, it is not blank. If you make the results of the formula a #NA error [=na()], this will prevent the value from plotting.Steve

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi Steve,
    I tried doing all that, understanding that formulas always return a number. I read several solutions and the one that seemed the simplest is to do a two column solution. one column has the formula and the second column simply equals the first column. but that didn't solve my problem. not sure if i'm doing the na correct.

    here is the formula in the cells is =SUM('sheet1'!F22,Sheet2!F22)
    the cell is formatted Accounting, so it looks like this: $ 5000.00 if there is something to return and it shows: $ - if there is no data to add.

    I tried to add this when i attempted the two column method. on the second column (which is the range for the chart data), i did this: so if column A1 had the above formula, I had B1: =IF(A1="","#N/A",A1).

    how should i do any of this different to get the chart to simply stop charting if the cell is zero?

    On a side note...why would excel make this so difficult or unintuitive? it seems like a simple click should do it. It seems that's what the "hidden and empty cell" button was to do, but it doesn't work.

    Thanks for your patience with me.
    Rick

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I tried to add this when i attempted the two column method. on the second column (which is the range for the chart data), i did this: so if column A1 had the above formula, I had B1: =IF(A1="","#N/A",A1)
    Your formula puts the literal text string "#N/A" into a cell. This is text that looks like the error not the error. You want a formula like:

    =IF(A1="",NA(),A1)

    The function NA results in a #N/A error.

    It seems that's what the "hidden and empty cell" button was to do, but it doesn't work.
    Yes it DOES work. As I mentioned in my original response, if a cell has something in it, it is NOT empty. Even the null string ("") in a cell makes the cell NOT empty. So unless you HIDE those cells (by hiding the rows) you can check the "hidden the empty" but since the cells you worry about are not hidden nor are they empty, they are charted (and text is plotted as a zero).

    The way around this is to use the #NA error which does not plot.

    Steve
    Last edited by sdckapr; 2012-01-10 at 07:21.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    A SUM formula will never return "", so you want:
    =IF(A1=0,NA(),A1)
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts
    I figured i had that wrong on the "N/A#" part, but i tried that after i had already done what you suggested ( i saw it in another thread) I redid the IF statement the way you described but it didn't work.

    Here's what I have:
    REMINDER NOTE: I have two columns, the first has the formulas which pulls data from different sheets and adds them. The adjacent column has the IF(A1="",NA(),A1) formula. I'll call the first column the HOST column and the 2nd column the CHART DATA column, since the Chart's data source is the 2nd column.

    I set up the data columns to go to the year 2030. However I only put the SUM formula through the year 2020. Years 2021-2030 are blank. This is how it looks:
    cell range from year 2005-2011- Has data since it could add up the filled cells and plots correctly on graph

    cell range from year 2012-2020- returns the Accounting version of zero "$ -", on both the HOST column and the CHART DATA column and not the expected "#N/A". This range plots as zero on the x axis...not what I want.

    Cell range from year 2021-2030-are blanks in the HOST column (since there are no formulas). The CHART DATA column returns the expected "#N/A". This range is not plotted on the chart (which is what I want, but for years 2012-2020 too)

    So I have been able to make the cell range without a formula show "#N/A" which gives me the desired result, but how can i make a cell that has a formula do that?
    Rick

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you want the formula to ignore zeroes as well you can use:

    =IF(or(A1="",A1=0),NA(),A1)

    If I am still missing what you are after, could you post a sample file and elaborate on the issue?

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi Steve,
    I attached a version of what I'm trying to do for you to look at and help.

    based on what i have explained so far, the data should be self explanatory, but here is the overview:
    Column b contains the formula summing data found in sheet 2 & Sheet 3. Column C contains data from Sheet 1.
    Column D is equal to column B, but contains the IF clause in order to produce the desired #N/A to get the graph to not plot.
    column E does the same thing except for column C

    You can see in the results and on the graph that through 2020 (which are the years I entered the formula) the graph is plotting zeros. from 2021-2030 I have no formula entered in B & C and i get the right result:"#N/A" and it does not plot anything from 2021-2030.

    So how can i tweak D & E's formula to produce the #N/A.
    thanks,
    Rick
    Attached Files Attached Files

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts
    I would change the formula in cell [D26] to:
    =IF(B26<1,NA(),B26)
    ..and change the formula in cell [E26] to:
    =IF(C26<1,NA(),C26)

    ..and then copy these formulas down.

    zeddy

  10. The Following 2 Users Say Thank You to zeddy For This Useful Post:

    Potts13 (2013-10-06),r9thomas (2012-01-11)

  11. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Thanks!!!! that did it!
    Rick

  12. #11
    New Lounger
    Join Date
    Feb 2013
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have the same problem statement that Rick had. I seem to be having problem implementing the solution that he used.

    The potentially unique part of my application.... My chart uses 2 axes. The first axis plots a stacked bar (to show plan values) and the second axis plots a stacked line (to show actual values). I'd like the "actual" lines to not plot for future time periods where there is no data.

    I have made sure my formula uses =NA() so that the result of the cell is "empty" and does not plot. I have also adjusted the Hidden/Empty cell option to "Gaps". (I also tried Zero for grins and giggles.) Both axes use the same number format (integers with no decimal places).

    Any reason why 2 axes or mixing columns/lines should prevent me from using this solution? anything I am missing?

    Thanks!

  13. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi

    Can you post a sample file?

    zeddy

  14. #13
    New Lounger
    Join Date
    Feb 2013
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Here's a generic version of the file.

    Sample.xlsx

  15. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi

    See attached file.
    I used dynamic range names for the data series you wanted to plot.
    Essentially, this defines the data source range based on current date etc.
    See if you can work it out from the attached file.
    If not, post back here for more guidance.

    zeddy
    Attached Files Attached Files

  16. The Following 2 Users Say Thank You to zeddy For This Useful Post:

    psymann (2014-12-04),thelontx (2013-03-04)

  17. #15
    New Lounger
    Join Date
    Mar 2013
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Omit graph plots

    I have prepared a line grah but it shows zero in the last as there is no date for that particular week.

    I do not need the zero line as the graph line should be continued without any breaks.


    For ex: From June 2012 to 21 nov 2012 i have date then the next data will be on December and the next will be in jan (in between we have weeks, we are calculating weeks) so there is no data after jan to till date.

    But my line graph shows zero for that week from jan to march

    Pls help us in this regard

Page 1 of 2 12 LastLast

Posting Permissions

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