Results 1 to 15 of 25

20120109, 10:54 #1
 Join Date
 Jan 2001
 Location
 Columbus, Ohio
 Posts
 255
 Thanks
 7
 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 20122020 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

20120109, 13:04 #2
 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

20120109, 21:48 #3
 Join Date
 Jan 2001
 Location
 Columbus, Ohio
 Posts
 255
 Thanks
 7
 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

20120110, 07:16 #4
 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)
=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.
The way around this is to use the #NA error which does not plot.
SteveLast edited by sdckapr; 20120110 at 07:21.

20120110, 08:45 #5
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,349
 Thanks
 4
 Thanked 228 Times in 210 Posts
A SUM formula will never return "", so you want:
=IF(A1=0,NA(),A1)Regards,
Rory
Microsoft MVP  Excel

20120110, 09:13 #6
 Join Date
 Jan 2001
 Location
 Columbus, Ohio
 Posts
 255
 Thanks
 7
 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 20212030 are blank. This is how it looks:
cell range from year 20052011 Has data since it could add up the filled cells and plots correctly on graph
cell range from year 20122020 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 20212030are 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 20122020 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

20120110, 09:34 #7
 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

20120111, 11:54 #8
 Join Date
 Jan 2001
 Location
 Columbus, Ohio
 Posts
 255
 Thanks
 7
 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 20212030 I have no formula entered in B & C and i get the right result:"#N/A" and it does not plot anything from 20212030.
So how can i tweak D & E's formula to produce the #N/A.
thanks,
Rick

20120111, 12:12 #9
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,412
 Thanks
 164
 Thanked 641 Times in 609 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


20120111, 13:26 #10
 Join Date
 Jan 2001
 Location
 Columbus, Ohio
 Posts
 255
 Thanks
 7
 Thanked 0 Times in 0 Posts
Thanks!!!! that did it!
Rick

20130226, 16:40 #11
 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!

20130227, 05:06 #12
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,412
 Thanks
 164
 Thanked 641 Times in 609 Posts
Hi
Can you post a sample file?
zeddy

20130301, 18:07 #13
 Join Date
 Feb 2013
 Posts
 3
 Thanks
 1
 Thanked 0 Times in 0 Posts
Here's a generic version of the file.
Sample.xlsx

20130302, 07:13 #14
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,412
 Thanks
 164
 Thanked 641 Times in 609 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


20130304, 06:11 #15
 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