Chart series (not starting at first year)

[David Adamson]

I currently have a chart series that ranges from 0 to 100 (thanks to Jon
Peltier) to reflect years. I can now add another series of data on top of
that. But what I was wondering is how can I change my series so that I can
show only the data for say year 20 to 40.



I have tried changing 'a' to 20 and 'c' to 40 and it retrieves the correct
data but puts the data over year 0 to 19.



Can someone please tell me what I am missing?



Cheers



David

---------------------------

Charts.Add

ActiveChart.ChartType = xlXYScatterSmoothNoMarkers

ActiveChart.Location Where:=xlLocationAsObject, Name:="Spread Data"

With ActiveChart

.HasTitle = False

.Axes(xlCategory, xlPrimary).HasTitle = False

.Axes(xlValue, xlPrimary).HasTitle = False



End With



With ActiveChart.Axes(xlCategory)

.MinimumScale = 0

.MaximumScale = 100

.MajorUnit = 10

End With



With ActiveChart.Axes(xlValue)

.MinimumScale = 0

End With

'add new data then delete legend

a = 1

r = 6

With Worksheets("Spread Data")

Set Title_Names1 = .Cells(r, 1)

End With



With ActiveChart

.SeriesCollection.NewSeries

.SeriesCollection(2).Name = Title_Names1

End With



'loop through data and make it update

For c = 1 To 100

With Worksheets("Spread Data")

Set Area_rng1 = Range(.Cells(r, 1 + a), .Cells(r, 2 + c))

End With



With ActiveChart

.SeriesCollection(2).values = Area_rng1

End With



Next c

 

[Jim Cone]

David,

If Tools | Options | Chart - "Not plotted ( leave gaps)" is checked then
1. you can change your data for the non-plotted periods to zero or
2. switch to an alternate data source that has zeros for the non-plotted periods.

Regards,
Jim Cone
San Francisco, CA

I currently have a chart series that ranges from 0 to 100 (thanks to Jon
Peltier) to reflect years. I can now add another series of data on top of
that. But what I was wondering is how can I change my series so that I can
show only the data for say year 20 to 40.

I have tried changing 'a' to 20 and 'c' to 40 and it retrieves the correct
data but puts the data over year 0 to 19.
Can someone please tell me what I am missing?
Cheers
David

 

[Geeves]

Thanks for that Jim,

I'll give it a go when I get back into the office.

I'll juts have to find a way to trigger it via code.

If not I'll use the 'union' function and insert dummy variables.

 

[Jim Cone]

David,

I said use "zeros", I should have said use "blank cells".

Regards,
Jim Cone
San Francisco, CA

 

[Jon Peltier]

David -

Each series in a line chart uses the same categories. So the first
series uses (e.g.) A2:A101 for X and B2:B101 for Y. If you want to use
C22:C41 for the second series' Y values, and have it plot at the same
categories as B22:B41, you need to offset the first data point with
twenty blank cells. This might mean doing some reconstruction in the
worksheet, if you are trying to avoid plotting any data that might be
present in C2:C21.

The alternative is to make an XY Scatter chart. Now your second series
can use A22:A41 and C22:C41 for its X and Y values. If the categories in
A are actual year values, you can even keep series 1 as a line series,
but add series 2 as an XY Scatter series.

People get tangled up in Excel's use of Line and Scatter terminology.
Many of the problems users have with Line charts and their X axes can be
answered with the suggestion to use XY Scatter charts instead. Either
type of series can be formatted the same, in terms of markers, lines,
etc. The difference is in how the X data is treated: a Scatter chart
treats these as actual numerical values, while a Line chart treats them
as non-numerical categories (or as dates in the mislabeled time-scale
category axis option).

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

 

[David Adamson]

Jon,

Thanks for the tips. I'll have a good play with it and see what I can do