Cumulative Year to Date Chart

[Guest]

I have a chart with time on the horizontal axis and a year to date total on the vertical axis. there's a "budget" and a "last year" line as well as the actual data, which is entered each day.

As data is entered, a line will appear for year to date actual to compare to the budget and last year data. The problem is that the line drops to zero at the current date, because the range contains zeros. I'd like the line to just stop, as it would if the range contained empty cells. Empty cells seem to be the only ones that get ignored. I've tried putting in a null string, but that gets treated like a zero. Is there a way to tell the chart to ignore zero values?

 

[Guest]

I've used the code below to clear the range the chart look at and then only fill in the cells I want.

Option Explicit
Private Sub Worksheet_Calculate()
Dim cEl As Range, CumulativeRange As Range
Application.EnableEvents = False
Set CumulativeRange = Range("CumulativeRange")
CumulativeRange.Clear
For Each cEl In CumulativeRange
If cEl.Offset(0, -1) > 0 Then cEl.FormulaR1C1 = "=rc[-1]+r[-1]c"
Next
Application.EnableEvents = True
End Sub

 

[Jon Peltier]

Rob -

You could also define dynamic ranges which detect how much room the data
takes up, and resize accordingly. Here are a few examples and links to
more examples:

http://peltiertech.com/Excel/Charts/Dynamics.html

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

I've used the code below to clear the range the chart look at and then only fill in the cells I want.

Option Explicit
Private Sub Worksheet_Calculate()
Dim cEl As Range, CumulativeRange As Range
Application.EnableEvents = False
Set CumulativeRange = Range("CumulativeRange")
CumulativeRange.Clear
For Each cEl In CumulativeRange
If cEl.Offset(0, -1) > 0 Then cEl.FormulaR1C1 = "=rc[-1]+r[-1]c"
Next
Application.EnableEvents = True
End Sub





:

I have a chart with time on the horizontal axis and a year to date total on the vertical axis. there's a "budget" and a "last year" line as well as the actual data, which is entered each day.

As data is entered, a line will appear for year to date actual to compare to the budget and last year data. The problem is that the line drops to zero at the current date, because the range contains zeros. I'd like the line to just stop, as it would if the range contained empty cells. Empty cells seem to be the only ones that get ignored. I've tried putting in a null string, but that gets treated like a zero. Is there a way to tell the chart to ignore zero values?