Problem with Chart drawing blank cells as zero

Hi

I am currently trying to create an excel line chart showing cumulative 
baseline vs Cumulative Actual vs forecast actual lines (the forecast line is 
a continuation of the Actual to date line) which I have done - but am trying 
to automate it so it is 'more user friendly' and can be created at the touch 
of a macro.
The problem I am having is that one of the formulae i am using looks up a 
date in the timescale and if it is in the future I want it to leave the cell 
bank (using the "" option" - problem is the chart then decides this is not a 
blank cell it is a zero and plots it accordingly. - Obviously I can "clear 
the contents" afterwards to put the chart back to where it should be - but is 
there another option ?
Please help -all comments appreciated -  is there any VB that can sort this ?

Thanks in advance 

Mat  
0
mat (11)
11/18/2008 3:58:05 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
605 Views

Similar Articles

[PageSpeed] 53

Hi,

Use NA() instead of "".
Or perhaps consider named ranges to only plot as far as is required.
http://peltiertech.com/Excel/Charts/Dynamics.html

Cheers
Andy

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Mat" <Mat@discussions.microsoft.com> wrote in message 
news:BAD75E79-4F78-4801-93AE-A9E333958D5A@microsoft.com...
> Hi
>
> I am currently trying to create an excel line chart showing cumulative
> baseline vs Cumulative Actual vs forecast actual lines (the forecast line 
> is
> a continuation of the Actual to date line) which I have done - but am 
> trying
> to automate it so it is 'more user friendly' and can be created at the 
> touch
> of a macro.
> The problem I am having is that one of the formulae i am using looks up a
> date in the timescale and if it is in the future I want it to leave the 
> cell
> bank (using the "" option" - problem is the chart then decides this is not 
> a
> blank cell it is a zero and plots it accordingly. - Obviously I can "clear
> the contents" afterwards to put the chart back to where it should be - but 
> is
> there another option ?
> Please help -all comments appreciated -  is there any VB that can sort 
> this ?
>
> Thanks in advance
>
> Mat 

0
andy9699 (3616)
11/18/2008 4:04:07 PM
Mat -

A cell with a formula by definition is not blank. Even if it returns "", 
that's not blank, that's just a short bit of text.

The best you can do for an XY or line chart is to change "" to NA() in your 
formula. This returns the unattractive #N/A error in the cell, but a chart 
will not plot a marker for this point. If your markers have lines connecting 
them, the line will connect the points on either side of the #N/A. This 
means you will not get a gap in the line. There are some heroic measures you 
can take to display an apparent gap, see Andy Pope's web site for an 
example, http://andypope.info/.

We have asked Microsoft to add a worksheet formula like BLANK() or NULL(), 
but they have not yet obliged us.

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


"Mat" <Mat@discussions.microsoft.com> wrote in message 
news:BAD75E79-4F78-4801-93AE-A9E333958D5A@microsoft.com...
> Hi
>
> I am currently trying to create an excel line chart showing cumulative
> baseline vs Cumulative Actual vs forecast actual lines (the forecast line 
> is
> a continuation of the Actual to date line) which I have done - but am 
> trying
> to automate it so it is 'more user friendly' and can be created at the 
> touch
> of a macro.
> The problem I am having is that one of the formulae i am using looks up a
> date in the timescale and if it is in the future I want it to leave the 
> cell
> bank (using the "" option" - problem is the chart then decides this is not 
> a
> blank cell it is a zero and plots it accordingly. - Obviously I can "clear
> the contents" afterwards to put the chart back to where it should be - but 
> is
> there another option ?
> Please help -all comments appreciated -  is there any VB that can sort 
> this ?
>
> Thanks in advance
>
> Mat 


0
jonxlmvpNO (4558)
11/18/2008 4:18:53 PM
Reply:

Similar Artilces:

Need to create a pie chart
I need to create a pie chart, and I am running into some problems. I has been a long time since I have last done this, and was hoping tha someone could help me. I have a column named COMPLETED. I this column it either has a Y or N. I need to have a Pie chart so that it shows the % of Completed Y and of Completed N. I am having some problems coming up with how to set this up. I hav tried the Chart Wizard, however I am just too confussed. PLEASE HELP!!!!!!:confused -- Message posted from http://www.ExcelForum.com If your data are in A2:A100 (with A1 as a header), then in some empty rang...

Recall button showing under transaction but Zero Items on hold
Hello, about a week ago I noticed that under transactions for every user its showing the recall button. the issue is there are no items on hold. is there a way to reset this so the hold option comes back up. I tried adding a new user and they see the recall button too, so its like the system thinks there items on hold but on the recall screen there are 0 items. Thanks for any help on this. BUMP This is a multi-part message in MIME format. ------=_NextPart_000_00C9_01C85E08.0A247590 Content-Type: multipart/alternative; boundary="----=_NextPart_001_00CA_01C85E08.0A247590" -...

Problem inserting a node with a namespace
This is so silly I must be doing something wrong! I need to insert (using VB.NET) a new node in a DOM; node has a namespace. I first create, under programme, basic structure, starting thus: <rdf:RDF xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" xmlns:prism="http://prismstandard.org/namespaces/1.2/basic/" xmlns:pcv="http://prismstandard.org/namespaces/1.2/pcv/"> Then I attempt to add a node with rdf namespace prefix. My call in skeleton is: CreateElement("rdf", "subje...

how do you make a line of a chart change color halfway through?
How could you change the color of a line in a line chart so that at some point on the graph the line changes color? Say like the line chart shows data over the months of a year but at month July, the line on the chart changes from blue to green and is green the rest of the months. I am asking about 1 line being two colors. Hi, You could format each segment of the line. Select the series and then use the Right cursor to move through the segments. Note if you only have 1 series changing a section of the series will cause the legend to report each segment. The other way is to use multip...

problem carrying over values
I have one form that has a button I''ll call it form1. The button looks at a text box for an ID and if its null it opens form2 and creates a new record in form2. If the text box is NOT null, it will go find the ID that's in the text box and filter it in form2. When the new record is created in form2, I need the ID to be put into the NULL text box in form1. I know how to do this however, if you open form2 on its own, then the error msg comes up that it can't find form1. I know why I'm getting this error, but don't know the fix for it. Is there a work around? Thanks...

Counting occurences of codes in cells
I have a spreadsheet with various codes separated by periods, in a single cell. Like: C T C,R T,C,R R and so forth. It's easy to count the single codes but how do I cound the "C" when it's with another character? There must be a simple way, without writing code, to do this. Is there? Thanks! maybe... =countif(a1:a10,"*" & "c" & "*") or =countif(a1:a10,"*c*") or if x99 contained the letter c. =countif(a1:a10,"*" & x99 & "*") salgud wrote: > > I have a spreadsheet with various codes separa...

Realteck 5.1 (6 channel) sound problem
Hi, I have finally got round to connecting all my speakers up to my fairly new PC. I have a Soundblaster desktop 5.1 speaker system and my new mobo, aa ASrock N68PV-GS, has 3 rear connectors for the onboard Realtek ALC662 system. I connect up my 3 cables, Front Green to Green on the mobo, middle black/blue and the centre/bass pink/orange to the mic in. The Realteck control panel objects to this mic connection. No matter what I try I get everything via the two front speakers. All is set up to 5.1 in the control panel, I have checked all my cables are well seated and in good ...

Force XmlSerializer to use explicit closing tags for zero-length strings?
Greetings, I am using the .Net Framework XmlSerializer to serialize a class that contains some string properties. The problem is that when a string contains a zero-length string ("") it is serialized as: <foo /> When what I would really like is: <foo></foo> Now I know the former is well-formed and all, but I really need the explicit closing tag. The XML file will be read by another system (out of my control) that requires the explicit closing tag. Any ideas here? Thank you. Regards, Scott Ballard <scott.ballard@gmail.com> wrote in message news:111...

License Problem
I have networked two macs and purchased and installed two copies of Office:Mac (2004). I now find that my second mac is reading the license info off the first one and not allowing me access on the second one. I have uninstalled and reinstalled the software on the second mac at least a dozen times, then discovered Microsoft's instructions "Error message when you try to start an Office 2004 for Mac program: "Microsoft Office 2004 is already in use". So I followed those instructions at least a dozen times to remove the Office 2004 for Mac licensing information and it has not...

Problem with code that inspects thread context and dumps stack trace
Hi! Some years ago, I had to debug a Java application that had a deadlock problem. It wasn't so hard, because I've been told about a JVM command (now, I don't even remember how to activate it!) that dumps the status of all threads in an app, including "what they are waiting for" (locks). Now I'm in the "real" world of C++, Win32 applications and MFC (no virtual machines around!). We have many multithreaded applications: what if some bug causes a dedlock, e.g. in a test scenario? We must inspect it, and I'd like to have a tool to simplify my life. Does s...

create chart /table excel-save, close & reopen colors change? Why
When I create a chart/table in Excel - save, close & reopen to use again, the colors have changed for my formating. How do I set the formating so that the colors stay the same - I have tried styles with no luck. The colors need to be the company approved. Thank you, Anne, I am unable to reproduce yr problem. Excel should NOT override yr manual settings. However,you can set yr company colours as default under Tools/Options/[Color tab]. Here are the colours Excel uses by default for the workbook. (Under that thin line you see the defaults for fill and line colours.) Hope this...

Problems adjusting to XP
I'm a moderately experienced user with two complaints: 1) I accidentally set the 'recently used file list' to zero, and I meant one. In the Options or wherever it is, now, the box is grayed out and I can't get it re-set to 1. Halp!! 2) 97% of my work is with mini-databases which I prefer to have in Excel, and the new Excel keeps reminding me and scolding me for having text labels which appear to be numbers [duh!!] And it scolds me when I sort. How can I tell Excel to stop treating me like an idjit? -- Regards, P D Sterling www.pdsterling.us 1) No Idea 2) Tools>...

cut/copy and paste forumla problem help
i am currently using the correlation formula to test on a large set of data: e.g. CORREL(IGR!C233:C262,IIA!C233:C262) IGR and IIA - they are a pair of data i place in 2 different worksheets, each has set of data, and I need to pull the data of C233 and C262 in IGR and IIA worksheet and I have many pairs in different worksheets E.G. RMR and RHR so I copy the forumla above and hope can only manually adjust the symbol from IGR and IIA to RMR and RHR when I copy it shift the number down =CORREL(IGR!C242:[/B]C271,IIA!C242:C271) from C233 to C242 and C 262 to C271 and I would have to ma...

Conditional Cell Category
Is it possible to make a cell's category conditional on the value in another cell? I would like to have a cell display its value as a currency if the cell before it says "Monthly", and as a percentage if the cell before it says "Annual". I am working in Excel 2003. Thank you, Trey ~ On 29 June, 15:39, one third <g...@hotmail.com> wrote: > Is it possible to make a cell's category conditional on the value in > another cell? > > I would like to have a cell display its value as a currency if the > cell before it says "Monthly", and as ...

Problems with customizing styles in the Tracking Gantt
Hi, I want to modify the appearance of critical tasks in the Tracking Gantt but somehow it doesn't work. When I open the Bar Styles dialog, I select the first row (Show for "Normal, Critical" tasks, From - Start, To - Finish). I want to change the default pattern to be tick since when I print the chart the critical tasks almost do not appear. However, when I choose another pattern nothing changes. There is another funny thing. When I double-click a particular task, I see under the Bar Text tab that it shows Baseline Duration to the left and % Complete to the right....

Align Line Charts By Milestone Dates?
I'm a chart neophyte. I have a worksheet with multiple line charts, each line depicting total hours per month per project. I would like to align the lines to a common milestone, like "start of detailed design". Each project of course has a different date for this milestone. Other than knowing that somewhere I'll have to specify the dates of the milestone for each project, I have no idea of what to do. Any help/insight will be appreciated. Thanks, Mike Each data series has a column of dates and a column of percent complete values. I'll use columns A and B in...

Assembly Entry Variance Account Blank
When we create and release an assembly entry then click the "Distribution" button it doesn't populate a variance account most of the time. I've checked the items in the item master and they all have a the correct assembly variance account, the components have stock available and are not discontinued, but it will not populate the account. I've found two BOM's that it will populate correctly on but I don't see any differences between the two. Is there any way I can get the assembly variance account to populate? Thanks, Eric ...

How to change cell data from all upper to title case
I need to change several columns of data from all upper case to Title Case. What formula should I use & How can I apply the formula to the data in each cell? Check out the Proper() function. It changes any case to title case, and can be copied from cell to cell to apply the formula to your data. Check out the Proper() function. It changes any case to title case and you can copy it to a range of cells to apply it to your data. You have other answers showing the use of the PROPER Function to act upon one cell at a time. If you want to change many cells at once you will need a mac...

MDB store encountered a problem and needed to close
I have a Exchange server 2003 Sp2 on Windows 2003 R2, Symantec MAil Security 5.0 with brithmail anti-spam In the last days I obtain the error reported at the end of this message every time I login in on Windows Server. Please tell me any usefull information Thanks in advance. Luke. ---------------------------------- Event Type: Information Event Source: Application Error Event Category: (100) Event ID: 1004 Date: 6/22/2006 Time: 5:10:59 PM User: N/A Computer: PostOffice Description: Reporting queued error: faulting application store.exe, version 6.5.7650.28, faulting module exsmtp....

Forum Navigation Funtionality Problems
I am having great difficulty navigating the Excel forum. For example when I clicked on the forum link to "Excel general questions" IE returns the error that the page cannot be found. I have found that if I first click on an Outlook forum and gain access to it, and then click on "Excel general questions" I will gain access immediately. After posting a question to the "Excel general questions" forum, and receiving an email response, the link in the email to the thread took me to an error message that the page was not found. I then navigated manually to the f...

Please Help strange problem
Hope someone can help with this after downloading a small excel file from a dealer site that is a genereated report, I can download it as an excel file, upon doing so and pasting it into my working sheet I end up with this � with the two dots above it at the end of the text in every cell, and when it is removed some cells change for example a cell which has a long number for a serial number will shorten as well where do I start the excel version is 2000 blacklabhfxnospam@hotmail.com remove no spam ...

determining macro status in a cell
Hi Is there a way to determine the status of macros (enabled or disabled within a cell formula? Thank -- Message posted from http://www.ExcelForum.com I don't believe this is possible. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "newengland >" <<newengland.11d8au@excelforum-nospam.com> wrote in message news:newengland.11d8au@excelforum-nospam.com... > Hi > > Is there a way to determine the status of macros (enabled or disabled) > within a cell formula? > > > Thanks > > > --...

line spacing in cell
I would like to type 3 pieces of text, each on a seperate line in one cell. I've looked under cell format Press Alt-Enter where you want to start a new line! Regards, Stefi „dlh” ezt írta: > I would like to type 3 pieces of text, each on a seperate line in one cell. > I've looked under cell format Thanks so much. Works great!! "Stefi" wrote: > Press Alt-Enter where you want to start a new line! > > Regards, > Stefi > > „dlh” ezt írta: > > > I would like to type 3 pieces of text, each on a seperate line in one cell. &...

Adding an Active X control to a cell
I would like to add a drop down lists to a bunch of cells. The control adds a box over the top of all the cells, trying to size it so that it fits over the top of the cell seems to be a rather inaccurate method of doing it. Hold down alt while you place it in the cell and it will fit the cell -- Regards, Peo Sjoblom "Rick" <rstenson@qualnetics.com> wrote in message news:317AA5B4-7970-45B2-8497-556670E48018@microsoft.com... > I would like to add a drop down lists to a bunch of cells. The control adds a box over the top of all the cells, trying to size it so that it f...

How do I auto-expanding SourceData range for charts
How do I auto-expand the SourceData range for charts when I add a new row to my data table. I have 49 charts that take 49 data tables. I'd like to be able to just add a new row to each table and have the charts automatically expand their SourceData ranges. Thanks. Vince - You can have your chart autoexpand when data are added. This is done for each series, not for the source data all at once, using dynamic defined names as the chart series data, as described in a couple examples and a lot of links on my web site: http://www.geocities.com/jonpeltier/Excel/Charts/Dynamics.html...