Line Graph Excluding 0 Values

Dear,

I have plotted a line graph consisting the data for the entire month. The 
data range is getting calculated automatically by a formula.

1 Dec - 100
2 Dec - 105
3 Dec - 0
4 Dec - 0
So on...

The data for the days starting from 3 Dec is zero and the line graph 
suddenly goes down to zero... 

Is there any way that the line graph picks the data excluding the zero 
values? I mean the line graph should only take the data for 1 Dec and 2 Dec 
without changing the data range. 

Thank you.
0
Utf
12/2/2009 7:19:01 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
1771 Views

Similar Articles

[PageSpeed] 36

one way might be to hide columns for 3 Dec, 4 Dec etc.

On 2 Gru, 08:19, Sasikiran <Sasiki...@discussions.microsoft.com>
wrote:
> Dear,
>
> I have plotted a line graph consisting the data for the entire month. The
> data range is getting calculated automatically by a formula.
>
> 1 Dec - 100
> 2 Dec - 105
> 3 Dec - 0
> 4 Dec - 0
> So on...
>
> The data for the days starting from 3 Dec is zero and the line graph
> suddenly goes down to zero...
>
> Is there any way that the line graph picks the data excluding the zero
> values? I mean the line graph should only take the data for 1 Dec and 2 Dec
> without changing the data range.
>
> Thank you.

0
Jarek
12/2/2009 7:56:28 AM
Replace zeros by =NA() in source data!

Regards,
Stefi

„Sasikiran” ezt írta:

> Dear,
> 
> I have plotted a line graph consisting the data for the entire month. The 
> data range is getting calculated automatically by a formula.
> 
> 1 Dec - 100
> 2 Dec - 105
> 3 Dec - 0
> 4 Dec - 0
> So on...
> 
> The data for the days starting from 3 Dec is zero and the line graph 
> suddenly goes down to zero... 
> 
> Is there any way that the line graph picks the data excluding the zero 
> values? I mean the line graph should only take the data for 1 Dec and 2 Dec 
> without changing the data range. 
> 
> Thank you.
0
Utf
12/2/2009 8:20:04 AM
Dear Stefi,

The 0 is a result of a formula. If I replace the formula with =NA(), the 
data is not reflecting in the line graph.

Is there any way to keep the formula as it it or tweak the formula such that 
it results empty cell and the zero value is not reflected in the graph?

Please suggest.

"Stefi" wrote:

> Replace zeros by =NA() in source data!
> 
> Regards,
> Stefi
> 
> „Sasikiran” ezt írta:
> 
> > Dear,
> > 
> > I have plotted a line graph consisting the data for the entire month. The 
> > data range is getting calculated automatically by a formula.
> > 
> > 1 Dec - 100
> > 2 Dec - 105
> > 3 Dec - 0
> > 4 Dec - 0
> > So on...
> > 
> > The data for the days starting from 3 Dec is zero and the line graph 
> > suddenly goes down to zero... 
> > 
> > Is there any way that the line graph picks the data excluding the zero 
> > values? I mean the line graph should only take the data for 1 Dec and 2 Dec 
> > without changing the data range. 
> > 
> > Thank you.
0
Utf
12/2/2009 9:03:01 AM
Try replacing yourformula by this:

=IF(yourformula=0,NA(),yourformula)

Regards,
Stefi

„Sasikiran” ezt írta:

> Dear Stefi,
> 
> The 0 is a result of a formula. If I replace the formula with =NA(), the 
> data is not reflecting in the line graph.
> 
> Is there any way to keep the formula as it it or tweak the formula such that 
> it results empty cell and the zero value is not reflected in the graph?
> 
> Please suggest.
> 
> "Stefi" wrote:
> 
> > Replace zeros by =NA() in source data!
> > 
> > Regards,
> > Stefi
> > 
> > „Sasikiran” ezt írta:
> > 
> > > Dear,
> > > 
> > > I have plotted a line graph consisting the data for the entire month. The 
> > > data range is getting calculated automatically by a formula.
> > > 
> > > 1 Dec - 100
> > > 2 Dec - 105
> > > 3 Dec - 0
> > > 4 Dec - 0
> > > So on...
> > > 
> > > The data for the days starting from 3 Dec is zero and the line graph 
> > > suddenly goes down to zero... 
> > > 
> > > Is there any way that the line graph picks the data excluding the zero 
> > > values? I mean the line graph should only take the data for 1 Dec and 2 Dec 
> > > without changing the data range. 
> > > 
> > > Thank you.
0
Utf
12/2/2009 10:47:01 AM
Thank you so much stefi... Its working :)

"Stefi" wrote:

> Try replacing yourformula by this:
> 
> =IF(yourformula=0,NA(),yourformula)
> 
> Regards,
> Stefi
> 
> „Sasikiran” ezt írta:
> 
> > Dear Stefi,
> > 
> > The 0 is a result of a formula. If I replace the formula with =NA(), the 
> > data is not reflecting in the line graph.
> > 
> > Is there any way to keep the formula as it it or tweak the formula such that 
> > it results empty cell and the zero value is not reflected in the graph?
> > 
> > Please suggest.
> > 
> > "Stefi" wrote:
> > 
> > > Replace zeros by =NA() in source data!
> > > 
> > > Regards,
> > > Stefi
> > > 
> > > „Sasikiran” ezt írta:
> > > 
> > > > Dear,
> > > > 
> > > > I have plotted a line graph consisting the data for the entire month. The 
> > > > data range is getting calculated automatically by a formula.
> > > > 
> > > > 1 Dec - 100
> > > > 2 Dec - 105
> > > > 3 Dec - 0
> > > > 4 Dec - 0
> > > > So on...
> > > > 
> > > > The data for the days starting from 3 Dec is zero and the line graph 
> > > > suddenly goes down to zero... 
> > > > 
> > > > Is there any way that the line graph picks the data excluding the zero 
> > > > values? I mean the line graph should only take the data for 1 Dec and 2 Dec 
> > > > without changing the data range. 
> > > > 
> > > > Thank you.
0
Utf
12/2/2009 12:55:01 PM
You are welcome! Thanks for the feedback!
Stefi

„Sasikiran” ezt írta:

> Thank you so much stefi... Its working :)
> 
> "Stefi" wrote:
> 
> > Try replacing yourformula by this:
> > 
> > =IF(yourformula=0,NA(),yourformula)
> > 
> > Regards,
> > Stefi
> > 
> > „Sasikiran” ezt írta:
> > 
> > > Dear Stefi,
> > > 
> > > The 0 is a result of a formula. If I replace the formula with =NA(), the 
> > > data is not reflecting in the line graph.
> > > 
> > > Is there any way to keep the formula as it it or tweak the formula such that 
> > > it results empty cell and the zero value is not reflected in the graph?
> > > 
> > > Please suggest.
> > > 
> > > "Stefi" wrote:
> > > 
> > > > Replace zeros by =NA() in source data!
> > > > 
> > > > Regards,
> > > > Stefi
> > > > 
> > > > „Sasikiran” ezt írta:
> > > > 
> > > > > Dear,
> > > > > 
> > > > > I have plotted a line graph consisting the data for the entire month. The 
> > > > > data range is getting calculated automatically by a formula.
> > > > > 
> > > > > 1 Dec - 100
> > > > > 2 Dec - 105
> > > > > 3 Dec - 0
> > > > > 4 Dec - 0
> > > > > So on...
> > > > > 
> > > > > The data for the days starting from 3 Dec is zero and the line graph 
> > > > > suddenly goes down to zero... 
> > > > > 
> > > > > Is there any way that the line graph picks the data excluding the zero 
> > > > > values? I mean the line graph should only take the data for 1 Dec and 2 Dec 
> > > > > without changing the data range. 
> > > > > 
> > > > > Thank you.
0
Utf
12/2/2009 3:20:01 PM
Reply:

Similar Artilces:

return value based on combo box selection
I have a combo box (cmbShipDate) in a form that is based on a table with drop down values of ShipDate1, ShipDate2, ShipDate3, etc. I have created a text box and I want the correct ship date to be displayed based on the combo box selection. These ship dates are all stored in a table called tblOrder. I thought I could use the DLookUp fuction, but I'm not doing something correctly. Thanks I don't understand what you mean by "based on a table with drop down values". And when you say the "ship dates are all stored in ... tblOrder", do you mean tha...

Unlinking tracked emails in CRM 3.0
I find that once an email is linked to/tracked by CRM (Customer, Client, Activity) it can't be unlinked! only deleted. For example; A client uses an old CRM generated email containing the tracking token to ask about a new problem. CRM automatically tracks the new email to the old CRM case and I can't untrack it to link it to a new case. Any information would be helpful... Hi, Check out "How to "untrack" Microsoft CRM Outlook e-mails" on following blog http://blog.sonomapartners.com/ -- PLEASE do click on Yes or No button if this post was helpful or not for ou...

Horizontal Lines/Graphics
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I'm trying to replicate the lines/dividers used in this sample r�sum�: <a href="http://www.resumesandcoverletters.com/Classic_Functional_Resume.pdf">http://www.resumesandcoverletters.com/Classic_Functional_Resume.pdf</a> <br><br>I can't figure out how to position the horizontal lines just right (i.e., I can move them, but they won't drag into exactly the right position I want) and I can't figure out how to make those sideways, stretched-out bracket-looking things. &...

Converting an MFC app from VS 6.0 to 2005
Hi, I'm just converting a VS 6.0 project to 2005. The project is compiled now but I had a problem with AfxGetThread() not working the same way on 2005. The MSDN documentation was very clear and I fixed it but ... What about other API's that have changed? Is there a list or a tool to find them and report any potential problems with the code? Otherwise I'll have to rely heavily on testing the entire app (which I've never used before!). Thanks. Peter "Peter Smithson" <Peter_Smithson@hotmail.com> wrote in message news:1160647234.909331.165340@i42g2000cwa.g...

eConnect 10.0 and BizTalk 2006R2
I successfully installed BizTalk 2006R2 on W2K3SP2 and downloaded eConnect 10.0 from Customersource (the download is dated 10/17/07 I believe). However despite the documentation stating that there was an adapter for Biztalk, I did not see any option to install the BizTalk adapter when I installed the SDK. Does eConnect 10.0 even recognize BizTalk 2006 R2? ...

Display/Hide a Form Control/Field Based On Another Field Value?
Hi all, I have to Combo boxes on a form. The first one I would always like users to see. The second, however, I would like hidden if the first box has certain values selected. So, if ComboBox1 has values of 1 or 2, I would like ComboBox2 to show as normal. If ComboBox1 has a value of 3, I would like ComboBox2 hidden. Is there a relatively simple way of doing this, if at all? Thanks for your help as always! --Eric You have to take care of 2 things: (1) When you move from record to record and the combo box contents changes (2) When the user changes the contents of the combo box For (1), inse...

Two Onenote windows on startup (command line switch)
Hi, I have a dual monitor setup, one being a tablet PC, and wanted to get two Onenote windows ready upon startup: - A normal window, opening my last used file and without full screen - let's consider this one the standard launched window, so it's already there by adding onenote.exe to the startup group, so no issues here. - The second window I would like to be on the tablet, and I believe I could get that extra screen by adding a second shortcut with the /sidenote switch (or can I not? Haven't tried as it's not my ideal scenario), but I would ideally want it to open a new On...

Can I add a baseline value to a chart?
I have a chart that shows results of tests taken on different dates to show improvement. I need to add a baseline to the chart showing the Desired Result. From an earlier reply: You can add the lines to the chart by using extra data series. Here is a collection of examples. http://peltiertech.com/Excel/Charts/RunChtLines.html http://peltiertech.com/Excel/Charts/AddLine.html http://tushar-mehta.com/excel/charts/straight_lines/index.html http://www.andypope.info/charts/averageline.htm -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jeanine" <Jeanine@...

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. 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 f...

Changing positive values to negative
I’m using excel 2007with windows xp pro. I use excel very infreuently and not since upgrading to 2007. I have a long column of dollar values that I need to change from positive to negative values such as: $123.45 ($123.45) I can accomplish the feat manually over many hours by F2>.HOME>- > ENTER> DOWN ARROW and repeat and repeat and repeat. Or I can use a macro. But using a macro has changed from years ago. When I tried the macro inserted a specific value of cell rather than the value of the selected cell. So that didn’t work. What can make this conversion easier? If t...

Problem with .Values
Hi all, I have a Sub for construct graphic but if the number of vx is too large, ERROR at .Values = Y. Why ? Thank you for your help, Serge Sub ConstruireGraphiqueParTableauxVBA() Dim X() As Double Dim Y() As Double, i As Long, vx As Double ReDim X(i), Y(i) Application.ScreenUpdating = False ici = ActiveSheet.Name '********************************************* For vx = -15 To 15 'If the number of vx is too large, ERROR 'at .Values = Y. Why ? '********************************************* X(i) = vx Y(i) = vx ^ 2 i = i + 1 ReDim Preserve X(i), Y(i) Next vx Cha...

adding values from 2 columns
I am trying to compose a personal cash flow chart where I can enter outgoings and income for every day of the month and see what the balance is on any day. I need to get a value in Col F which adds the value in Col C (daily income), subtracts the value in Col E (daily outgoings) and shows the balance. Also I may have to specify several rows if I have multiple outgoings on a particular day. Any ideas gratefully received. Gaby Sounds like you want a checkbook register. Something like this? A B C D E Start Bal $1,023.32 Date Item Expense Deposit Balance $10.12 $1,013.20 $23.3...

minimum for casual cells but >0
I have big table with vendors, and I have to find the lowest price for each item, but prices are like: A B C D E F G H I etc. Item x 128 175 0 215 the problem is there are also columns with quantities which shouldn't be included I don't want to include columns e.g. B, D, F etc. AND find minimum price but >0. so I can't use: MIN(C1,E1,G1,I1...) because I'll get 0 as result. I'm looking for the simplest solution. Appreciate for any help. Ctrl/shift/ente...

Ann: GoogXL 1.0
Internet Search with GoogXL: GoogXL is a free Microsoft ExcelT add-in that provides Internet search using the Google API from within Excel. Features: Quick Search box in integrated toolbar Advanced Search dialog Results entered in active worksheet at selected active cell More Information at http://www.bitogo.com//infopath/googxl.html -- InfoPath, Inc. http://www.bitogo.com/infopath/index.html ...

CRM 4.0
Hi How does one configure a WF Local Service / Data Exchange Service in CRM 4.0? In a custom WF host I can accomplish this by specifying the service in the host application's configuration file. Is this approach possible with CRM 4.0? I understand that the workflow runtime is hosted by the CRM Asynchronous Service, and I have been able to register custom WF Activity as detailed in the CRM SDK documentation. However I can find no references to registering / configuring a local service. Any help much appreciated! Regards, Gareth On Oct 23, 1:38=A0pm, rgdav...@gmail.com wrote: > H...

returning vlookup values for blank cells
I have a spreadsheet that lists "soccer players" by name down the first colunm and "time in game" across the top and the position they play in array. I then use vlookup for another spreedsheet by "position" down the first column, time across the top and puts the players name into the positions. All this works fine. Since there are 5 more kids than positions, the orginal spreedsheet has blanks when the kids are out of the game. How do I use vlookup or other to extract the 5 sub'd out kids at the bottom of the 2nd spreadsheet? It only returns the nam...

Parse multiple text lines into 1 line in excel
help. I am an excel beginner and can't find out how to turn multipl lines of text into 1 row in excel. It's probably really easy but m manual is USELESS. Can anyone help ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com debbie You're a little short on details. If nothing below fits the bill post back. "Multiple lines" is how many and is each line in a separate cell down one column? Do you want all lines to go into one cell? You can use this form...

Nested graph
I am trying to create a column graph with three variables: month (jan, feb .... dec), site (site 1, site 2) and action (action 1, action 2). I want the month on the X axis and have two columns, one with action 1 & 2 for site 1, and the other with action 1 & 2 for site two. I tried the "Stacked Column" feature which is close to what I want but not quite. I want jan data for site 1 & 2 to be right next to each other (no space). I also want to differentiate the colors/patterns for the sites: I want each site to have its own color. Right now I have two colors: o...

posting values
on excel,i've been using v-lookup to get values in the same row from one column but i was wondering if it was possible to use data in 2 columns and get a value in the same row to transfer into my data sheet. I looked at every function in the program but can't seem to find the way to do that. if anyone knows how to do this, pls respond to this. thanks-lisa Lisa, VLOOKUP can match a value in the first column and return the value from any other column - and the same row as the matched value - just expand the second range to include the data that you want to have returned, and use t...

AX for Retail. Is that RMS 3.0?
Hey, Does anyone know much about AX for Retail or has heard anything about it? I am using RMS 2.0.0126 now on Vista 32 bit but am wanting to upgrade to Windows 7 64 bit and would like to know if AX for Retail is better then RMS. Thanks, -- Steve Steve, Please drop an email to get the details. MV nj.tech@hotmail.com "Steve @ S&S Tire" wrote: > Hey, > > Does anyone know much about AX for Retail or has heard anything about it? I > am using RMS 2.0.0126 now on Vista 32 bit but am wanting to upgrade to > Windows 7 64 bit and would li...

Does Microsoft CRM 3.0 extend the Active Directory schema?
Q. Does Microsoft CRM 3.0 extend the Active Directory schema? No, the AD schema is not extended. It simply creates an add'l Organization Unit and some security groups under that OU. No changes that can't be simply undone... "Rifat Yavuz" <Rifat Yavuz@discussions.microsoft.com> wrote in message news:B5E9FBF3-D8B5-4735-AC87-494AA2B81599@microsoft.com... > Q. Does Microsoft CRM 3.0 extend the Active Directory schema? thanks Chris Rifat Yavuz "Chris Resch" wrote: > No, the AD schema is not extended. It simply creates an add'l Organization...

CRM 3.0 Demo db ?
How can I get Demo data in my CRM env ? I used Adventure Work Cycle as my organization name..but everything is empty. Sam Hi Sam! "Sam" wrote > How can I get Demo data in my CRM env ? I used Adventure Work Cycle as my > organization name..but everything is empty. Read chapter 18 of the implementation guide: Microsoft CRM 3.0 Implementation Guide http://www.microsoft.com/downloads/details.aspx?familyid=1ff067f8-4f77-40f0-ae9c-68ada7d4f16a and have a look at the "SampleData"-folder on your server-installation medium. Cheers Arne Janning On the 3.0 serv...

Exchange 2003 / IIS 6.0 WMI Install problem
Some, about half of our fresh installed Windows 2003/Exchange 2003 servers are missing WMI performance classes for monitoring. The only class we know of that does not appear to be installed is: Win32_PerfRawData_SMTPSVC_SMTPServer Contains many sub functions and events that can be monitored. Is there a way to copy the WMI class from one machine that has them to another? We have tried reinstalling SMTP and Exchange 2003, but may have failed because the WMI service kept starting up on its own during the reinstall process, are there any other services or applications we can uninsta...

Save spreadsheet using cell values as filename
I am trying to use a command button to save a spreadsheet using cell values as the file name from the tab Master. For example I want to use cells B2 (Tony.Dungate) and F2 (2010.March) to create a file name of Tony.Dungate.2010.March.xls. Essentialy I would need something like : Master!B2&.&Master!G2&".xls" Can someone advise me on the correct code to do this. I have been trying to use the following without success: Const sRoot As String = "V:\Database Logs\" With ActiveWorkbook .SaveAs Filename:=sRoot & _ ....

I want to format a cell based on an adjacent cells value
I would like a cell automatically formatted in the same way a conditional format works - but the format to be based on the value of an adjacent cell. Any ideas as I cant work out how or if I can achieve this Cumbo, Use Conditional Formatting, but change cell valve is to formula is and use something like this, will change the cell if A1=25 =$A$1=25, so if you select B5 and put this in conditional formatting cell B5 will change when A1 = 25 -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on an...