How to ignore zero values when plotting a graph

Using Excel 2003. I have a data range for a graph. The values in the cells 
are the results of a simple If function - If(m28>0,n28,0). The results are 
taken from a larger data input exercise. But, the graph line (a simple 
graph!) plots the FALSE value (0) when I would like there to really be no 
value & hence no plotted point if the result is FALSE.
0
Lavis (1)
2/15/2005 5:23:04 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
626 Views

Similar Articles

[PageSpeed] 1

Phil -

Change this:

   If(m28>0,n28,0)

to this:

   If(m28>0,n28,NA())

This results in the ugly #N/A error in the cell, but it makes the chart 
ignore the point. Debra Dalgleish shows how to hide the ugliness with 
conditional formatting:

   http://contextures.com/xlCondFormat03.html#Errors

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Phil Lavis wrote:

> Using Excel 2003. I have a data range for a graph. The values in the cells 
> are the results of a simple If function - If(m28>0,n28,0). The results are 
> taken from a larger data input exercise. But, the graph line (a simple 
> graph!) plots the FALSE value (0) when I would like there to really be no 
> value & hence no plotted point if the result is FALSE.
0
2/15/2005 6:29:14 PM
Hi Jon,

Using a standard line chart, I seem to be getting an interpolated value with 
NA() as opposed to a gap in the line.  Could I be missing a setting somewhere?

For example, my original data is on the left side below and charted data is 
on the right.  Assuming a blank column between the two sets with the data 
starting in cell A1, I've added this formula to cell E1 and copied down the 
column:

=IF(B1>0,B1,NA())

a	6		a	6
b	4		b	4
c	0		c	#N/A
d	5		d	5
e	0		e	#N/A
f	3		f	3

Excel seems to interpolate the line in column E rather than leaving a gap.

Tushar Mehta has this information on his site - does this still apply?

http://www.tushar-mehta.com/excel/software/na_discontinuity/

Thanks.

John Mansfield


"Jon Peltier" wrote:

> Phil -
> 
> Change this:
> 
>    If(m28>0,n28,0)
> 
> to this:
> 
>    If(m28>0,n28,NA())
> 
> This results in the ugly #N/A error in the cell, but it makes the chart 
> ignore the point. Debra Dalgleish shows how to hide the ugliness with 
> conditional formatting:
> 
>    http://contextures.com/xlCondFormat03.html#Errors
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
> 
> Phil Lavis wrote:
> 
> > Using Excel 2003. I have a data range for a graph. The values in the cells 
> > are the results of a simple If function - If(m28>0,n28,0). The results are 
> > taken from a larger data input exercise. But, the graph line (a simple 
> > graph!) plots the FALSE value (0) when I would like there to really be no 
> > value & hence no plotted point if the result is FALSE.
> 
0
2/15/2005 7:14:54 PM
No, you are not missing anything.  XL will only interpolate over NA()s.  
It won't create gaps.  To get gaps, you have to use a programmatic 
solution such as
Chart gap for N/A
http://www.tushar-mehta.com/excel/software/na_discontinuity/index.html

However, because of a bug introduced with XL2002 (2000?) and not yet 
fixed means even the code doesn't work with a line chart, though it 
continues to work just fine with a XY Scatter chart.

-- 
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <B67FA397-07B4-4B35-B3D2-0811647B8C38@microsoft.com>, 
JohnMansfield@discussions.microsoft.com says...
> Hi Jon,
> 
> Using a standard line chart, I seem to be getting an interpolated value with 
> NA() as opposed to a gap in the line.  Could I be missing a setting somewhere?
> 
> For example, my original data is on the left side below and charted data is 
> on the right.  Assuming a blank column between the two sets with the data 
> starting in cell A1, I've added this formula to cell E1 and copied down the 
> column:
> 
> =IF(B1>0,B1,NA())
> 
> a	6		a	6
> b	4		b	4
> c	0		c	#N/A
> d	5		d	5
> e	0		e	#N/A
> f	3		f	3
> 
> Excel seems to interpolate the line in column E rather than leaving a gap.
> 
> Tushar Mehta has this information on his site - does this still apply?
> 
> http://www.tushar-mehta.com/excel/software/na_discontinuity/
> 
> Thanks.
> 
> John Mansfield
> 
> 
> "Jon Peltier" wrote:
> 
> > Phil -
> > 
> > Change this:
> > 
> >    If(m28>0,n28,0)
> > 
> > to this:
> > 
> >    If(m28>0,n28,NA())
> > 
> > This results in the ugly #N/A error in the cell, but it makes the chart 
> > ignore the point. Debra Dalgleish shows how to hide the ugliness with 
> > conditional formatting:
> > 
> >    http://contextures.com/xlCondFormat03.html#Errors
> > 
> > - Jon
> > -------
> > Jon Peltier, Microsoft Excel MVP
> > Peltier Technical Services
> > Tutorials and Custom Solutions
> > http://PeltierTech.com/
> > _______
> > 
> > Phil Lavis wrote:
> > 
> > > Using Excel 2003. I have a data range for a graph. The values in the cells 
> > > are the results of a simple If function - If(m28>0,n28,0). The results are 
> > > taken from a larger data input exercise. But, the graph line (a simple 
> > > graph!) plots the FALSE value (0) when I would like there to really be no 
> > > value & hence no plotted point if the result is FALSE.
> > 
> 
0
2/15/2005 10:02:12 PM
Reply:

Similar Artilces:

rollback combobox to 'BeforeUpdate' value
How do I set a combobox to a previous value? When finished updating a particular record the user selects an identifer in a combobox to advance to the next desired record. I use the BeforeUpdate event of the combobox to ask a yes/no question. If NO I want to stay with the current record and this works. the problem is that the combobox has been advanced to the new selection and stays there. Now I have the identifer for one record in the combo box but the form is still (as it should be) on the current record. I need to roll back the combobox to what it was before the update bu...

Public calendars in OWA: is there a way to IGNORE the time zone of the local PC?
We plan on using a web based Exchange 2003 public calendar for our main school calendar of events. The way Exchange uses time zones creates potential for distaster if the client's time zone is set incorrectly the appointment appears at the "wrong" time. Now ideally all client computers should be set to Vienna time and all is fine, but we are dealing with an international community of 3000 people checking the calendar from home; from experience I know that trying to support such a group is a nightmare (having a non-english speaker with Windows in Japanese or Arabic is not atypica...

Insert empty numeric value
Dear all, In VB, I have three textbox which are amount1,amount2 and amount3. After user enter the value in the textbox, I will insert the value into Access table. The table have three columns amount1 , amount2 and amount3, and all are nummeric Type. However, if the user do not enter any value in textbox . The insert statement will become as follows: Insert into table1 (amount1,amount2,amount3) values (,,) Then access complain that there is syntax error in insert statement. Does that mean I cannot insert empty value for the numeric value in access.? How to solve this problem. Than...

Subtracting value from main form
I have a borrow module which will alow user to return item separately. So, I have get the structure of returning it separately. In my main form is the borrowing item, with the loaned quantity and the owed quantity (will be calculated). In the subform, there is the returning transaction. User will need to key in the quantity returned and it will be automatically deducted from the quantity owed. But how am I supposed to get the quantity deducted while it 1 is in main form and the other is in subform? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-fo...

How can build second gaussian with other deviation value?
Hello, I want to know how can build second gaussian [with deviation (1) with given datas. Please see in this lin (http://zmo.pochta.ru/Excel/). Thanks, ZM -- zm ----------------------------------------------------------------------- zmo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3476 View this thread: http://www.excelforum.com/showthread.php?threadid=54522 X Y -11.06 229.90 -10.79 234.50 -10.52 275.70 -10.25 316.50 -9.98 344.80 -9.71 440.50 -9.44 507.60 -9.17 573.10 -8.9 692.50 -8.63 766.30 -8.36 943.60 -8.09 1095.90 -7.82 1291.30 -7.55 1614.00 -7.28 ...

Get values from validation list; else type in values?
On Sheet1 I have a three-column table: ItemName, Value1, Value 2. I created a dynamic named range for ItemName so I can use these names in a drop-down list in Sheet2 via Data Validation. That way, I can add new items to the list and they will show up in the drop-down list. On Sheet2 I have a "calculator" that will need the values from the table on Sheet1. So when I use the drop-down in Col A to select an ItemName, Value1 for that item appears in Col B and Value2 appears in Col C. I also need to allow the user to type in an item not on the list and type in the values associated w...

How do I find a value on a line?
I made a line graph of data to use as a calibration. I know the y value and I want to find the X value. Is there a way that I find find this specifically on the line without using a trendline formula or guessing by looking at the gridlines? The only way to find a specific value is to use the formula. Rgs, Bou If you can accept piece-wise linear interpolation, see Interactive Chart http://www.tushar- mehta.com/excel/software/interactive_chart_display/index.html -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity s...

Graph over a time span
Hi, can anyone please help? Is there a way to graph from a span of time. We have Members with certain benefits that is good forever basically and then Members by their Contract St and End Dt and Members in special programs. So the data looks like for example: Member StDt EndDt ContractFees Contract Type Member A 1/1/2006 12/31/2009 $500 Program C Member B 1/1/2006 doesn't expire $2000 Program A Member C 6/1/2006 5/1/2010 $1000 Program B So I'd like to be able to count the # of ...

How to print a graph of x=y at 45 degrees
How can I make it appear correctly on paper? Gordon, Set up two sets of points: 0 0 1, 1 Plot them with an x-y scatter chart, line style. Set the x and y axis scales the same, if necessary, to get the resulting plot to be at 45�. Print it, and hope for the best. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "gordon158" <gordon158@discussions.microsoft.com> wrote in message news:2C5B1255-334F-41A3-A5E5-55516CFABA6A@microsoft.com... > How can I make it appear correctly on paper? ...

Replace null value with the previous value?
I have a database that was just imported that has approximately 388000 records. The problem is that there is information about a person in multiple different records but the name did not come across with each record. (So I have 10 records with information for a certain name, but the name only appears in field 1 of the first record and not the subsequent 9, etc.) I need to create a query or expression that will fill field 1 with the preceding value if it is null. This way I will have all the information for field 1 in a manner that I can link and combine data. Simply I need to fil...

Displaying Multiple Excel files, Not plotting zero values
Hello, I have two questions: 1) How ccan I display more than one Excel workbook in different window? When I open more than one Excel workbook, I want to display them side by side to make some comparisons. When I open one other closes. Is there a way to open many Excel windows? 2) I have a data set with lots of zero values. I use scatter plot. I want to only display non-zero values. How can I do it? Thank you. Athena 1) With both workbooks open, choose Window>Arrange Select an Arrange option, click OK 2) Create a chart from all the data. Apply an AutoFilter to the list (Cus...

Calculating values for empty cells.
Hello. I have a very simple problem that I cannot find the answer to. I have data in two columns, some of the data in one of the columns is missing and I want to automatically extrapolate what the data should be based on the trend. How can I get Excel to fill in empty values without overwriting the known values. Below is a sample of my data. 1500 1600 1700 1800 4000 1887 5700 1900 5500 1910 7300 1912 8100 1920 8800 1926 10100 1930 11900 1936 12200 1938 -- Ryan Taylor rtaylor@stgeorgeconsulting.com Not sure what yo...

How to use MSExcel to plot Column A against Col B?
I want to plot col A (x axis) against col B ( y axis). I cant seem to do it. Can anyone here please give me step by step primer. Thanks in advance. All I get is the graph of (1,2,............n) (x axis) against the n values of either col A or B. i.e 2 graphs instead of one. (In the old Lotus this was so simple: select the column for the X axis and then select the col for the Y and press enter, and you'd get the chart) Why is it so diff in Excel? Select the entire range you want to graph such as a2:b44>insert>chart>>>> -- Don Guillett SalesAid Software dguillett1@au...

Default value for custom field?
How can I populate a custom field with a default value? Specifically, I have a custom field "DisplayName" associated with the Quote Detail object. I want initially to populate this field with the value of the product name field when a new product is added to a quote. The user can then edit the DisplayName custom field if desired. The DisplayName custom field will be used as the product name on a Crystal Reports quote form. ...

Chart to show Portfolio Value over time?
I'm using Money 2003. I would like to be able to see the $ value I have in my portfolio over time. So that I can weep. Money does have a chart view that allows you to see the PRICE history for a given stock over time - but not the dollar value of your investment in the stock over time. In fact, I can't seem to find a view at all that shows you the net value of your portfolio/individual stocks changing over time. The best I've been able to do is to use the "Net Worth" report and unselect all the other accounts. This has insufficient granularity (months instea...

Mystery Constant Causes #Value Error
I guess I stumped everyone in excel.worksheetfunctions group 'cause no one answered July 28 post. Hope someone here has the answer. A spreadsheet displays the #Value error in many cells. Each of these cells contains a formula that simply sums 3 non-contiguous cells in the same column e.g. =SUM(B16,B14,B12). The formula toolbar indicates the reason for the error is that a cell or cells in the range contains a constant which cannot be evaluated. However, no data is visible in the referenced cells (or formula bar when they are clicked on). There are no named ranges that I can see. If I selec...

Bar Graph #2
I have a bar graph and woulld like to have one of the bars change colou depending on the value of a cell (1 - Green, 2 - Orange, 3 - Red). dont have a lot of experiance when it comes to coding graphs. Please can you help -- ceem ----------------------------------------------------------------------- ceemo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1065 View this thread: http://www.excelforum.com/showthread.php?threadid=55115 Hi, No need for code if you use some worksheet formula and multiple series. http://peltiertech.com/Excel/Charts/ConditionalChart1...

how do i change the default value of measure from points to inche.
how do i change the default value of measure from points to inches when setting the width and hight of cells? You don't. Excel uses only points for these measures best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "yoyo4u" <yoyo4u@discussions.microsoft.com> wrote in message news:33420157-6E05-4A55-9003-088D731E495E@microsoft.com... > how do i change the default value of measure from points to inches when > setting the width and hight of cells? yoyo Row heights are measured in points. There are 72 points to an inch. Th...

OnSave function
Hi all, I am currently returning a value from the Parent Customer ID field in Contacts using OnSave . These values are being passed into a Web Service and onto another database. Code below: var lookupItem = new Array; lookupItem = crmForm.all.parentcustomerid.DataValue; if (lookupItem[0] != null) { var sAccountID = lookupItem[0].name; } The Problem I am getting is when I return a value from the lookup like "A & E Builder", the string is truncated and I am left with 'A'. Does anyone no a workaround for this? I really need to return the full text value as it appears i...

Null not equal to zero
How can I make one cell equal to the text of another cell - without the null value turning to zero? Example: A1=A2, but if A1 is empty, I do not want the return value of zero. In A2 enter: =IF(A1="","",A1) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "SLD" <anonymous@discussions.microsoft.com> wrote in message news:38ed01c4c13c$6d652440$a301280a@phx.gbl... How can I make one cell equal to the text of another cell - witho...

How to display zeros at the beginning of a number?
I have inventory numbers that start with zeros. How do I format cells to display these zeros. ex../. 000456789 FYI The numbers do not have the same amount of zeros in front of them. Thanks hi, use the custom format 000000000 -- isabelle Le 2011-04-21 07:08, Larry Allen a �crit : > I have inventory numbers that start with zeros. How do I format cells > to display these zeros. ex../. 000456789 > FYI The numbers do not have the same amount of zeros in front of them. > Thanks Format as Isabelle suggests or preface with an apostrophe which won't show in the cell. You can pre...

excel sum values if cell matches a value
I am trying to analyze a survey. Results are like: A B C D E F G H I J K Q1 ? 1 1 1 1 Q2 ? Q3 ? 1 Q4 ? Q5 ? 1 1 1 1 Q6 ? 1 1 1 1 Q7 ? 1 1 1 1 1 I have about 30 questions and 200 responses (going up to column FR). The one represents they checked that box. In column B I want to add up all the responses of that row is they selected Q6. So in my example Question 1 B should equal 1 because I only would select the G response. And then for question 5, I want to sum up the people who selected Question 6, which would give me a ...

Limit value for Storage Groups
Hello, we are running Exchange 2003 Standard edition. The limitation on Storage Groups for e.g. "Issue warning at(KB)" is default set by 2097151. I read that i can change this value by using ADSI Edit and edit the values mDBOverHardQuotaLimit mDBOverQuotaLimit mDBStorageQuota But each of them has a lots of values and i could not find out which one i have to edit. Could you please give me an advice? TIA helpi If you want to set a value bigger than 2GB for all users on an entire mailbox store, bring up the properties of that mailbox store in ADSIEdit and set the mDBStorageQuot...

Line graph along a single axis
Using Excel 2000. I have data I want to display in a line graph along a single (x) axis. I have a series of 50 distances between 0 and 1 and want to display them in graphical form. To show the variability between each of the distances I wanted to draw a single horizontal line (min 0 and max 1) - ie x axis but no y axis - and plot each distance as a vertical line along that horizontal line. This would grapically display the distances between each point. Can't get Excel to do this. Can anyone help? Phil Conaghan ------------------------------------------------ ~~ Message posted from http:...

custom report not showing values
I have uploaded a very simple SRS report that includes "out of box fields" and custom fields. When I run the report, instead of my custom field values showing in the report, I get the id of the value. What am I missing? -Melissa ...