Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Problem with Chart drawing blank cells as zero
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Problem with Chart drawing blank cells as zero
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drawing a Blank | Excel Discussion (Misc queries) | |||
Lookup/count blank cells problem | Excel Discussion (Misc queries) | |||
problem with drawing vertical line on chart | Charts and Charting in Excel | |||
Problem with Blank cells | Excel Discussion (Misc queries) | |||
CONCATENATE problem with blank cells | Excel Discussion (Misc queries) |