How can I get the zero values at the end of a chart not to show?

I have a chart getting data from a spreadsheet, where data is added one day 
at a time, for an entire month.

After adding the data for the first day, the data point shows, but the line 
connecting the data point in the chart immediately drops to zero, since data 
for the remaining days is not available.

Is there a way to just have the line connecting the data points show only 
where there is data, and not show anything for the upcoming days where data 
is not available?

Thanks.
0
wabmreo (2)
12/1/2005 4:58:05 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
440 Views

Similar Articles

[PageSpeed] 3

1) use a dynamic chart - see 
http://www.stfx.ca/people/bliengme/ExcelTips/Dynamic.htm
or
2) rather than blanks for missing data use =NA()
or
3) click on chart and use Tools|Options, open CHART tab, check the option 
Plot empty cell as - not plotted

best wishes
-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"wabmreo" <wabmreo@discussions.microsoft.com> wrote in message 
news:0F6FAB1E-D8EA-4311-8853-AC0B590FB664@microsoft.com...
>I have a chart getting data from a spreadsheet, where data is added one day
> at a time, for an entire month.
>
> After adding the data for the first day, the data point shows, but the 
> line
> connecting the data point in the chart immediately drops to zero, since 
> data
> for the remaining days is not available.
>
> Is there a way to just have the line connecting the data points show only
> where there is data, and not show anything for the upcoming days where 
> data
> is not available?
>
> Thanks. 


0
bliengme5824 (3040)
12/1/2005 5:09:46 PM
The formula that is in the link below (option 1), once I entered it into the 
INSERT/NAME/DEFINE box, is not being recognized as a range, so when I modify 
the series it won't accept it...

It SHOULD recognize it as a range, correct?

thanks

"Bernard Liengme" wrote:

> 1) use a dynamic chart - see 
> http://www.stfx.ca/people/bliengme/ExcelTips/Dynamic.htm
> or
> 2) rather than blanks for missing data use =NA()
> or
> 3) click on chart and use Tools|Options, open CHART tab, check the option 
> Plot empty cell as - not plotted
> 
> best wishes
> -- 
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
> 
> "wabmreo" <wabmreo@discussions.microsoft.com> wrote in message 
> news:0F6FAB1E-D8EA-4311-8853-AC0B590FB664@microsoft.com...
> >I have a chart getting data from a spreadsheet, where data is added one day
> > at a time, for an entire month.
> >
> > After adding the data for the first day, the data point shows, but the 
> > line
> > connecting the data point in the chart immediately drops to zero, since 
> > data
> > for the remaining days is not available.
> >
> > Is there a way to just have the line connecting the data points show only
> > where there is data, and not show anything for the upcoming days where 
> > data
> > is not available?
> >
> > Thanks. 
> 
> 
> 
0
wabmreo (2)
12/1/2005 8:45:03 PM
Try =SERIES(Sheet1!$B$1,Dynamic.xls!date,Dynamic.xls!sales,1)
where Dynamic!.xsl is the name of the file from a previous save.

-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"wabmreo" <wabmreo@discussions.microsoft.com> wrote in message 
news:2B1BD1BE-BA84-402D-8E65-59CFDD88E5F4@microsoft.com...
> The formula that is in the link below (option 1), once I entered it into 
> the
> INSERT/NAME/DEFINE box, is not being recognized as a range, so when I 
> modify
> the series it won't accept it...
>
> It SHOULD recognize it as a range, correct?
>
> thanks
>
> "Bernard Liengme" wrote:
>
>> 1) use a dynamic chart - see
>> http://www.stfx.ca/people/bliengme/ExcelTips/Dynamic.htm
>> or
>> 2) rather than blanks for missing data use =NA()
>> or
>> 3) click on chart and use Tools|Options, open CHART tab, check the option
>> Plot empty cell as - not plotted
>>
>> best wishes
>> -- 
>> Bernard V Liengme
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> "wabmreo" <wabmreo@discussions.microsoft.com> wrote in message
>> news:0F6FAB1E-D8EA-4311-8853-AC0B590FB664@microsoft.com...
>> >I have a chart getting data from a spreadsheet, where data is added one 
>> >day
>> > at a time, for an entire month.
>> >
>> > After adding the data for the first day, the data point shows, but the
>> > line
>> > connecting the data point in the chart immediately drops to zero, since
>> > data
>> > for the remaining days is not available.
>> >
>> > Is there a way to just have the line connecting the data points show 
>> > only
>> > where there is data, and not show anything for the upcoming days where
>> > data
>> > is not available?
>> >
>> > Thanks.
>>
>>
>> 


0
bliengme5824 (3040)
12/2/2005 11:01:03 PM
Reply:

Similar Artilces:

Programatically format Axis Values
I have created a Chart control programatically. I set the minimum value of the horizontal axis by using the following code Axis ax = chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary) as Axis; ax.MinimumScale = 10000.0; I need to change the format of the values displayed in the axis from number to currency instead of number. How do I do this programatically? How can I programatically give format code for the axis. Thanks in advance. ...

find & replace value a cell
Hi I found a helpful code from Mr.Thomlinson about find last item (Q1 in row 5) ___A___B____C___ 1|Q1 aaa ZZ 2|Q1 bbb ZZ 3|Q1 ccc BB 4|Q1 ddd HH 5|Q1 eee GG 6|Q2 fff ZZ 7|Q2 ggg MM the code is: Public Sub FindLast() Dim rngFound As Range Dim rngToSearch As Range Set rngToSearch = Range("A2", Cells(Rows.Count, "A").End(xlUp)) Set rngFound = rngToSearch.Find(What:="Q1", _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ ...

SUBTRACTING TIME VALUES INSTEAD OF DECIMAL!!
Please help! I have used the 00:\00:\00 format to input times without having to press the colon every time. Now though, when Excel tries to minus one time from another, because of the custom format it only recognizes times as numbers... e.g. 12:15:30 - 12:14:50 Excel thinks this equals 00:00:80!! Can anybody help? How do I get it to recognise that the answer is 00:01:20 ? Thank you See previous post .misc -- HTH RP (remove nothere from the email address if mailing direct) "Lexicon" <Lexicon@discussions.microsoft.com> wrote in message news:5D1A072F-2DD2-49CA-8569...

show date file updated?
Can I have a field that automatically shows the date when the file was last updated? You can use code like Sub LastSaveTime() With ThisWorkbook If .FullName <> vbNullString Then .Worksheets("Sheet1").Range("A1").Value = _ FileDateTime(.FullName) End If End With End Sub This will put the last saved date and time into cell A1 on Sheet1. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Chris Hughes" <Chris Hughes@discu...

System headed South.. What can I do?
2 days ago.. all Ok.. 1 day ago, see wierd behavior as I double click on a .xls worksheet or launch Excel in my Office 2002XP.. I get a dialogue box immediately asking me to Install a Windows XP PRO module that is on the CD-ROM.. I have Win XP HOME.. and still see thhis error after reboot/ virus scan and registry cleanup..does same thing under different login names.. This morning, i boot up see an error box and repeadedly must close the dialog box to get rid of hyroglyphic foriegn text, and upon closing last of 10 or so repeating error boxes, my cursor disappears completely.. rebo...

If Checkbox is checked show msgbox
On my form's field (DRAWING_NUMBER) I need Access to look to see if a checkbox (RFP_Issued) is checked in the table (ER TABLE). If it is, I need a message box to popup. I've coded it many ways and receive run-time error 2465 or 13. Following is my current code: Private Sub DRAWING_NUMBER_BeforeUpdate(Cancel As Integer) If ([ER TABLE].[RFP Issued]) = 0 Then MsgBox "A RFP has been issued on this drawing." & vbLf & _ " Nofify Engineering Manager or Administrator before making any changes." End If End Sub Thank you i...

hairpulling-trouble with pasting a chart into word
Hi there, I do hope someone can help me - I'm going nuts. When I do this job at work, there is no problem at all, so am confuse why my laptop at home won't play ball. I have made sure (at least think I have) that all the settings are the same. I have a word document open and an excel sheet with the chart I want t copy into word, I uncheck the 'size with window' box (from tools>option - chart tab), yet, the second I paste into word it - sizes with th window, into a squished chart. What am I doing wrong? any help would be appreciated, Flos -- flos -------------------------...

Timeline Chart
Hello, I need to create a timeline chart showing if Phase1 finishes in 2 months then Phase 2 starts. I tried many examples from internet but it doens't work. Can someone please help me as i need this for my presentation. Thanks and Regards Pooja Have you looked here: http://peltiertech.com/Excel/Charts/GanttChart.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Urgent question regarding LIST" <UrgentquestionregardingLIST@discussions.microsoft.com> wrote in message news:1C3ABEC4-831D-480B-A845-46E44D90...

I accidiently deleted a 12 page workbook, how do I get it back?
I was copying a worksheet to start a new, 12 month, 12 sheets, new workbook and when I copied the page to start the new worksheet, the sheet I pasted onto new doc, was okay but I have to adjust all the margins, etc. However, when I went back to the 2009 doc, I clicked on the page to release it from being copied again, and the whole 2009 - 12 worksheets - where gone. Help me PLEASE, I need the 2009 document.....Thanks!!!!! Hi, List of options:- 1. use your backup. You do have one don't you!! 2. Close without saving. You'll lose the work you've done but should ge...

How do I show hidden column and row headings in excel?
How can I display hidden column and row headings in excel? Excel displays only unhidden rows and columns, and their headings. But if you make the width or height small enough, it might still be possible to see the heading and not the data. However, this becomes more difficult as you move to column AB or row 101. "Mildred" <Mildred@discussions.microsoft.com> wrote in message news:D7D83F4B-289A-49A6-9057-23365EAFB300@microsoft.com... > How can I display hidden column and row headings in excel? ...

Dynamic data selection for charts
Hi, I want to create a chart with dynamic data from a drop down. Like if i select option 1 in the data validation drop down the chart should populate with data from sheet 1. and if i select 2 then it should populate with sheet 2 data. Please suggest a way to do the same thanks sudheera Hi, You need to set up dynamic named ranges. http://peltiertech.com/Excel/Charts/Dynamics.html And here is some information on charting from selected data http://peltiertech.com/Excel/Charts/ChartByControl.html Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "sudheera&q...

tree chart
I have a homework assignment for a finance class that requires building a binomial tree. The tree has been built in excel cells as illustrated below with stock prices and option prices. Although not a requirement for my class, I was wondering if it is possible to plot this distribution on a chart. 50 45 40 xxx 55 50 xxx 45 xxx xxx 3.0 2.5 2.3 xxx 3.5 3.0 xxx 3.4 xxx ...

Can I Customize Outlook 2003 Map Link?
In the Outlook 2003 contact form, the "map link" button attempts to display the location of the contact based on the address information. This feature used to work well, but lately it is extremely slow and often doesn't produce results. I would prefer to use Google Maps. Is there a registry entry I can use to accomplish this? The page http://www.outlook-tips.net/howto/displaymap.htm describes how to do this for earlier Outlook versions but this appears to break Outlook 2003. Do I have a workable option? AFAIK, you cannot customize it. it's reportedly broken for older v...

Project invoices can be deleted
When I create a billing entry in project, it automatically assigns it the next transaction number. I'll save it to a batch so that I can preview/print the individual invoice. If I Delete the invoice (after it has been saved), rather than post it, I cannot find a record of it anywhere. Further, the next invoice created will use the next sequential number, leaving a gap in the invoice numbers. I find this to be a poor control issue. GP should replace the Delete button with a Void button, thus leaving a nice audit trail and no missing invoices. ---------------- This post is a sugg...

can't download my msn hotmail on outlook express
Hi Iam a user of MSxp and want to know why Outlook express doesn't let me get my hotmail e-mail, it just stays checking for new messages from hotmail and that's it for the rest of the night. This newsgroup is for support of Outlook 97, 98, 2000 & 2002 from the Office family for Windows PCs. For Outlook Express (OE) support try posting in one of these newsgroups: microsoft.public.inetexplorer.ie4.outlookexpress for OE 4.x microsoft.public.windows.inetexplorer.ie5.outlookexpress for OE 5.x http://support.microsoft.com/newsgroups/default.aspx?ICP=GSS3&NewsGroup=microsoft.publi...

How can I put a slash in a zero in Office 97
How can I put a slash in a zero in Office 97 Find a font that supports slashed 0's. Google will help you find lots--some will be free, others not. I found this a long time ago: http://www.qsl.net/wa6axe/fonts.html rmpete wrote: > > How can I put a slash in a zero in Office 97 -- Dave Peterson Thanks, Dave, I found two fonts I like, AnonymousTT and Monaco, with slashed zeros. I also found that you can add a slash to Arial with ALT+0216. Thanks again "Dave Peterson" wrote: > Find a font that supports slashed 0's. > > Google will help you find lots--so...

trying to set chart width
my chart width can change due to setting axes, etc and i want to keep it a certain size have tried code like below, when the 'width' number i thought was points 72 per inch to keep chart at 6" but not working, can this be done or is it a 'relative' size ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveSheet.Shapes("Chart 1").Width 452, msoFalse, msoScaleFromTopLeft I prefer to work with the ChartObject instead of the Shape: ActiveSheet.ChartObjects("Chart 1").Width = 452 which see...

How can I attach a hyperlinked object in a workbook cell to email
I've very limited knowledge regarding programming in VBA, and would like to create a control button whereby hyperlinked objects within excel cells can be selected and sent as attachments to predetermined recipients for action automatically, and get a response back when that action has been completed. the idea being to have the same function would be available available on every row in the shhet of the workbook as required, such that the control button could be copied into each row as required. Any assistance would be greatly appreciated. ...

How can I find font?
I create a club monthly newsletter using Pub 2007. Its contents come from many sources--club members, websites, etc., etc. Occasionally when opening my monthly 'creation', I see the note that one of the fonts is 'not recognized' and I'm asked what font I want to substitute. I understand the theory behind this..... But--how can I find just where the 'unrecognized' font is used in my publication? I've gone through this issue lots of times and can't find it. (The font is "Times") Any pointers would be appreciated. Usually the font will be...

Outlook 2000 can't send mail over Exchange 5.0 if OS is XP Pro
Hi, maybe you guys heard about this problem and could give me a hint: Windows 2000 Server (SP3) with Exchange 5.0 (SP3) XP Pro (SP1) or Windows 2000 Pro with Outlook 2000 Clients. I can send mails from the Windows 2000 Pro clients, but with the "same" setup I can't on the XP Pro clients. I get two errormails back, which say that there is a network error. But the network is ok (can ping and get the server shares), I even can receive mails. I assume it is a authentication problem, but I don't know anymore where to look at. Please help. TIA Alex ...

OnReceive is not getting called?? #3
Scott McPhillips [MVP] wrote: > OnReceive is a message handler, so it will only be called if your app > is > processing windows messages. If you have anything like a Sleep wait > or > infinite loop executing then no messages are processed and OnReceive > will not be called. I.e., it is not a callback, it is a message > handler. > > Is all of your socket code in the main thread? There are other > complications if you are using multiple threads. > > -- > Scott McPhillips [VC++ MVP] [/B] Dear Scott, thank you for your time. this is actually an examp...

get spinning pinwheel when Word opens up...
Am running OS 10.3.6 (dual G5) with office 2004 academic version and all of a sudden when I open up word or Excel or powerpoint I the get an eternal pinwheel (I let it go overnight the other night). It seems to be hung up on "optimizing font menu". I've re-installed, but it didn't help. I put office v.X back on the computer and it runs fine. Anybody got any ideas? In article <1102690790.301771.21130@z14g2000cwz.googlegroups.com>, aaron@timecodenola.com wrote: > Am running OS 10.3.6 (dual G5) with office 2004 academic version and > all of a sudden when I ope...

Common Function for getting userform information
Hi, My macro has a lot of userform and multi listbox within userform (each userform has unique name). how can I use a function to get the selected items in different listbox? eg. fmMyPlot.lb_Prof_xAxis fmMyPlot.lb_Prof_y1Axis fmMyPlot.lb_Std_Gp fmMyPlot.lb_Std_Series fmMyTemplate.lb_temp1 fmMyTemplate.lb_temp2 any ideas? thx Sub Test() Dim i As Long, n As Long Dim s As String Dim arr Dim lb As MSForms.ListBox Set lb = UserForm1.ListBox1 n = getSelected(lb, arr) If n = -1 Then s = "no items selected" Else s = arr(0...

Cannot get access to Reports/System Settings in CRM 3
Hey When i click on Reports, i get this error: Error An error has occurred. For more information, contact your system administrator. when i run http://servername/reportserver it is OK. But when i try to access http://servername/reports then The XML page cannot be displayed Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later. -------------------------------------------------------------------------------- A name was started with an invalid character. Error processing resource 'http://servername/reports/'....

how can i print postcard onto A5 with 2 copies to a page
how can i print postcard onto A5 with 2 copies to a page without any margin If your postcard is the standard 14.65cm x 11.05cm you can't. If you don't care if it is standard or not just divide your A5 stock in half, each card will be 14.8cm x 10.5cm -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "shree" <shree@discussions.microsoft.com> wrote in message news:4444CA0A-6BD2-4724-A512-6DE9744F1623@microsoft.com... > how can i print postcard onto A5 with 2 copies to a page without any margin Hi Mary, than...