Suppres Zero or empty Cell value in a line graph

Hi I'm using Office 2007. I have two charts using data from the same sheet. 
The second graph is a copy of the first. In the first graph, the empty and 
zero value cells are not displayed. In the second graph, the zero value cells 
is displayed (draged to zero) although the option "connect points with line" 
is checked.
The strange thing: if I change the Y-data to another part of the sheet, it 
is no longer dragged to zero. Even if the cell is empty it's still dragged to 
zero
0
E (96)
4/14/2009 1:56:01 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
436 Views

Similar Articles

[PageSpeed] 55

A formula that returns "" is not an empty cell, it's a formula (or it's a 
small piece of text). If you want the line to connect the points on either 
side, substitute NA() for "" in your formula.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Michael van E" <Michael van E@discussions.microsoft.com> wrote in message 
news:5D4B8887-EDF4-44B3-A310-C0BACB1CF4DA@microsoft.com...
> Hi I'm using Office 2007. I have two charts using data from the same 
> sheet.
> The second graph is a copy of the first. In the first graph, the empty and
> zero value cells are not displayed. In the second graph, the zero value 
> cells
> is displayed (draged to zero) although the option "connect points with 
> line"
> is checked.
> The strange thing: if I change the Y-data to another part of the sheet, it
> is no longer dragged to zero. Even if the cell is empty it's still dragged 
> to
> zero
> 


0
jonxlmvpNO (4558)
4/14/2009 2:35:59 PM
Reply:

Similar Artilces:

Trapping a value to use in my form
I am quite new to this and need some help. I have a form with a record source of a query. In the query I have the criteria set to ask the user a question in a popup box which searches a table for that input which is then reported back to the form to view EmployeeNumberQuery is as follows: Field: Employee Table: Employee_Number Criteria: [Enter the Employee Number] <-this is the value I want to capture and display in the form This query also has other fields that are reported back to the form that match the entered value The Employee_Listing_Table is as follows: Form Hea...

Active cell
Hello, this is my VBA Macro: I want to select "clastrow" as my active cell. is that possible ? Thanks, Sub SumM() Dim clastrow As Long Dim olast As Range clastrow = Cells(Rows.Count, "I").End(xlUp).Row MsgBox clastrow Set olast = Range("I" & clastrow) isum = Application.WorksheetFunction.Sum(Range("I4:I" & olast)) ActiveCell.Value = isum End Sub Not quite Jeff, You get the last row, cLastRow, but then use that cell rather than the row in the formula. Try Sub SumM() Dim clastrow As Long Dim olast As Range Dim iSum clastrow = Cells(Rows.Co...

Newibe, Passing value to the MFC activeX wrapper class. void putref_SQLPane(LPDISPATCH * newValue) Please help
Hi, I am using VS2003 VC7.1 MFC, and it has created a wrapper class for an activeX that I am trying to use. if I look at the activeX's documentation it says the following: Private Sub Form_Load() With qbQueryBuilder1 Set .SQLPane = qbSQLPane1 End With End Sub the equivalent ( Set .SQLPane = qbSQLPane1 ) wrapper function that VS2003 created for me is: void putref_SQLPane(LPDISPATCH * newValue) { static BYTE parms[] = VTS_PDISPATCH ; InvokeHelper(0x68030007, DISPATCH_PROPERTYPUTREF, VT_EMPTY, NULL, parms, newValue); } How do I pass a putref_SQLPane the proper LPDI...

How do I include cell contents from a form in generic statements/strings within the form?
I have a form that contains NAME in cell A:1, and GENDER in cell B:2. I want to build a library of generic statements along the lines of: NAME tries hard, but if HE/SHE tried harder HIS/HER results would be much better. How can I make these statements get NAME from A:1, and derive HE or SHE and HIS or HER from GENDER in B:2? =A1&" tries hard, but if "&IF(B1="M","he","she")&" tried harder "&IF(B1="M","his","her")&" results would be much better" -- HTH Bob (there's no ...

lookup multiple values in single cell
Hello there - Im looking for some help with a lookup. I have two spreadsheets. One with a simple list of numeric values: Spreadsheet A 1004 24965 66435 25459 7785 1008 26885 The other has multiple values in a single cell: Spreadsheet B 1004,24965,66435,25459 7785 1008 26885 The delimiter does vary, but it is always a non numeric. Id like to be able to find the row number in spreadsheet B where m value from Spreadsheet A appears. Thanks very much for your time. Andrew -- boneshom --------------------------------------------------...

How to convert a dotted line to a solid line in a line graph
How do you convert a dotted line between two points to a solid line in a line graph Click once to select the line, then click on a line segment to select just that segment, then right click and choose Format Point, and select the appropriate formatting in the dialog. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Sharlz" <Sharlz@discussions.microsoft.com> wrote in message news:190231A9-9979-451E-B840-186BF33B1B89@microsoft.com... > How do you convert a dotted line between two points to a soli...

How to show actual values in a pivot grap/table
Hi Can anyone help me with this - I can't get it working. I have at data list like the following Month Week Day New Working Standby 10 44 29 0 0 0 10 44 30 6 1 0 10 44 31 6 1 0 11 44 1 6 1 0 11 44 2 6 2 0 11 44 3 5 2 0 Where New, Working, Standby, ... are a track of the current numbers of each of these states. What I want is to put these data into a PivotTable/graph showing the progress of these states: Data Month Week Count of New Count of Working 10 40 13 13 41 14 14 42 14 14 43 14 14 44 7 7 10 Total 62 62 11 44 7 7 45 14 14 46 14 14 47 14 14 48 11 11 11 Total 60 60 1...

Windows 7: Vertical dotted lines in Explorer?
Hello, is there any way to make the vertical dotted lines connecting the folders/directories in the left-hand Explorer bar visible? The functionality must be present in Windows 7, as seen in regedit: http://www.unawave.de/medien/Windows-7/Tipps/Registry-Key-0CD7A5C0-9F37-11CE-AE65-08002B2E1262.png http://www.unawave.de/windows-7-tipps/zip-ordner.html Is there such a secret setting somewhere inside Windows? TIA Karsten PS: Is there a Windows 7 NG anywhere? Karsten Wutzke <kwutzke@web.de> wrote: >PS: Is there a Windows 7 NG anywhere? The MS web forums, where t...

scatter/category/line (?)
I have the following excerpt: Geo Year % NE 1999 25 NE 2000 30 NE 1999 22 NW 1998 15 NW 1999 20 Cannot figure out what parameters, types, etc, I am looking for to accomplish one (or both) of the following: First graph - X Axis Year - Y Axis % - color coded series by Geo Basically, something that just shows me the dots that represent where the geographies were by year. I will then remove the line segments and have just the data points. Pretty sure this is just a straight line graph, except as you'll notice, the year/geo co...

Link Chart with data cell (cl
This was your questio Hi I want to link all the points in my xychart with the rows of the data that are in the chart. i.e. after a mouseclick on the chart data the user will be redirected to the data row The idea is to create a link to a text cell, situated in the same row as the data, which explains the details to each point Thanks in advance for your help Mattia ...

Grid lines
I am using Office XP. I am working with word and I inserted a table and a pasted images into each cell. Now I am copying the whole document and I am pasting into PAINT. Now I hide the gridlines in word bur when I paste into Paint some of the grid lines still show up. Why? Sol Likely because it is an Image, the grids are still there, just not visible in Word. S1L1Y1 wrote: > I am using Office XP. I am working with word and I inserted a table and a > pasted images into each cell. Now I am copying the whole document and I am > pasting into PAINT. Now I hide the ...

Market Values Incorrect in Portfolio View
When I look at my portfolio the Market Value shown does not equal the current value times the quantity of stock for my stocks. It appears to be off by about 2x the amount I calculate. The real odd thing is the sum shown at the bottom is correct. What am I doing wrong? In microsoft.public.money, Kevin Kiefer <Kevin Kiefer@discussions.microsoft.com> wrote: >When I look at my portfolio the Market Value shown does not equal the current >value times the quantity of stock for my stocks. It appears to be off by >about 2x the amount I calculate. > >The real odd thing is...

Deleting Blank Characters in a Cell
I have a list of names with email addresses of attendees of a seminar series that I am trying to compile into a list that can be copied and pasted into an email (using BCC as the methodology for emailing). The challenge that I have is that when these files were exported from the database to excel (not sure how), all of the cells contain exactly 100 characters even though the email address may only contain 13-20 characters. Is there a way to "trim" the blank characters after the last character that is used by a letter instead of blank character field? Thanks in advance! PZan --...

splitting text from one cell
I need to remove specified data from a single cell and place on the same row in another column. Not all data will be the same eg 100mg, 200mg etc Not too clear; what data? Do you want to extract the number from the text? This will extract 100 from 100mg and 5 from 5mg: =--LEFT(A1,FIND("m",A1)-1) Any help? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "rogera" <rogera@discussions.microsoft.com> wrote in message news:98106D4C-04D2-4A2D-B293-9F5BBF0F04EB@microsoft.com... >I need to remove specified data from a single cell and place on th...

Locked hidden cells
Is there any way of stopping someone getting the values of locked hidden cells on a protected worksheet by linking to them on another worksheet? "John Bundy" <jmbundy@gmail.com(remove)> wrote > The right person can get into anything they want regardless of protection, > the more secure you make it the more people you stop but there are always > some. As far as linking them to another sheet, if they will break > protection > on one it is just as easy to do on two so I wouldn't bother Thanks. It seems ridiculous that you can hide data by protecting the...

Fill Down to Last Value
I have 2 spreadsheets (Sheet 1 and Sheet 2). Sheet 1 has one header row and sheet 2 has 2 header rows. I am using the following to count the number of rows in Sheet 1 and copy a formula to every row in Sheet 2 based on the count from Sheet 1. With Worksheets("Sheet1") lStop = .Cells(.Rows.Count, "A").End(xlUp).Row End With Worksheets("Sheet2").Range("3:" & lStop + 1).FillDown The formula is perfect except when I only have one entry in Sheet 1. In this case the formula copies the second header row in Sheet 2 instead...

Conditional Formating for a cell based on another cell's value
Hi all: I was wondering if there was a way to put a conditional formating on a cell based on the values of another cell. What I mean is that, if Cell A is >5 , make cell b Blue, not just cell A. Likewise, if Cell A is <5, make both cell a and b red. On the same topic, is it possible to put more than 3 conditions on a conditional formating? Thanks, Steve o This might point you in the right direction. Sometimes you need a row formatted according to the item in A1. Use a formula in the conditional formatting in type in something like this... =$A1<5 This will apply the cond...

How to link the content of a cell with a related list of items
I have a workbook that I created for tst item analysis. On the main worksheet is used to enter sudent's response for every multiple-choice question. on another worksheet which is the summary, I wrote a formula that allows me to sknow how many students choose choice 1, choice 2, choice 3,choice 4 for a given question. My challenge is to click on that number of student in the cell to display the list of students who chose cjoice 1, 2, 3, or 4. How canI do that please? -- Ti Moy Here's a formulas model to achieve the underlying objectives: http://cjoint.com/?chjHCSWehb ...

Two formulas in on cell based on two numbers in another cell?
Hi, Not sure this is possible but...I have a cell that has a number range in it and based on an amount in another cell want to calculate a new range. For example: Initial Range: A1 = 10 - 12 Calc Amount: A2 = 5 Final Range: A3 = 50 - 60 I think I can get the results by concatenating two formulas I'm just not sure how to enter the original numbers (A1) or how to distinguish between the two in the final formula (A3) Using Excel 2003. Hope this makes sense. Thanks. I would put the range in two different cells (eg A1 and B1). Then the multiplication is easy. If you ...

Line selection for formating in Excel chart
Have "plain vanilla" line chart in excel spread sheet. Data series consist of several lines closely space. Normally I can select and RClick a single series for formating such as line color and thickness. Is it possible to simultaneously select all data series to change a common format such as line thickness. I'd like to do this without individually selecting each line while the control key is depressed. (lazy and not that good-a shot). ~Dave Hi, No you can not select multiple series, like you can with shape objects. The only time saver is, select 1 on the series and the f...

Compiling MFC project from command line...
Hi, How do I compile and link MFC activex project from command line in MSVC 6.0 Is there any tool which generates such info... Help ! sandSpiderX If you look in the project settings for your ActiveX project, you will see the command line that is used by the IDE for building the project. You should be able to use that command line directly. "sandSpider" <m74.piscean@gmail.com> wrote in message news:1120655631.691634.137580@o13g2000cwo.googlegroups.com... > Hi, > > How do I compile and link MFC activex project from command line in MSVC > 6.0 > > Is the...

Write-Output of XML value has type format like "System.Xml.X..."
Hi, I want to output some value using Write-Output or Write-Host along with some comments. After reading the xml file and while executing the write-output command, its not printing the value, its printing the variable type along with xml structure not the value :( Please let me know how to print the correct output. See below the example for details. --------------------------------------------------------------------------------------------------------------------------- Variable.xml ------------------------------------------------------------------------------------...

Need to verify cell location before running a macro, how?
I am requesting users to click on a cell in column B (in which they are actually selecting a row of reference information), then to click on an icon which launches a macro that runs relevant to the row selected in column B. I would like Excel to verify that the selected cell is actually in column B prior to running the macro. If a cell in column B is not selected, I want to present a pop up message reminding them to select a cell in column B. Please help! If ActiveCell.Column <> 2 Then MsgBox "Don't Do That" Exit Sub End If -- Jim Cone Portland, Oregon USA http:...

Selecting a named range, the name of the named range is in a cell
What i want to do is the following : I have a cell which i have named "ExtraRisk". This cell contains text : example "Electricity", which is also the name of a range. Now i want to select this named range "Electricity" & copy the range How do i do this. What i have so far is : Sub Risicos_toevoegen() ..... Sheets("MASTER").Select 'I'm stuck here.... Application.Goto Reference:="ExtraRisk" 'I do not want to copy the named range "ExtraRisk", but i want to copy the named range where the va...

auto email if cell value changed
I'm using a workbook to schedule staff. Each month is a worksheet then I have some counts that are done. The days of the month are in the top row and the names of the staff in the first column. We've set up autorefresh on web pages so the schedule is always available but... people still don't notice shift changes. I want excel to generate an email to the person (we're running exchange too) if a cell value is changed. I was thinking of a couple of steps. 1. So they only get emails about the current month and the month ahead -- Have a cell at the top with YES/NO in i...