How do I get 3 series in sync with the x-axis?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Long version of my question:

When I put three series in one chart (for example a line chart with a
time-scale as the x-axis) the data items in the first series are distributed
properly along the x-axis according to their associated dates, but the items
that belong to the other two series (fewer items than the first series
posesses) are displayed in the same positions with respect to the x-axis as
the initial items in the first series and therefore are displayed as
belonging to the wrong dates.

If the first series has two items occuring on the same date, then every
series has two items occuring on that date, whether or not the data agrees.

This happens with integers and such as well -- not just with dates. It's as
though the x-axis is only for the first series and the items of other series
just piggyback the first n items of the first series, where n is the numer of
items in the secondary or tertiary series.

Short version of my question:

Why is the chart ignoring the associated date data of all but the first
series? Perhaps I only need somehow to inform it or are Excel charts not
capable of this?

Thanks for your time.
 
(a) Consider a XY Scatter chart
(b) If you must use a Line chart, see if setting the x-axis to type
Time helps. With the chart selected, select Chart | Chart Options... |
Axes tab. In there, for the Primary Category (X) axis, set the type to
Time (not Automatic and not Category).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
(a) Scatter charts seems to have the same problem. So far I've tried line,
column, bar, and area, as well to no avail.

(b) Yep, the x-axis already has its Type set to Time.

Thank you for the suggestions nonetheless.

Here's an example for anyone who wants to test it out:
(This would be the REALLY long version of my question.)

Let's assume the first date is in cell A1 and the first integer is in cell B1.

1/1/2004 1
1/3/2004 3
1/4/2004 5
1/7/2004 7
1/1/2004 22
1/5/2004 44
1/7/2004 66

Insert a chart. In the Series tab:
Make a new series named Series1and set it to B1:B4.
Make a new series named Series2 and set it to B5:B7.
Set "Catagory (X) axis labels" to A1:A4.
When you get to the Axes tab, set Category (X) axis to Time-scale.

Check it out. Series1 is fine, appearing on the 1st, 3rd, 4th, and 7th.
Series2 which should appear on the 1st, 5th, and 7th instead appears on the
1st, 3rd, and 4th, which are by no coincidence the first three dates in
Series1.

(By the way, including the dates related to Series2 in the time-scale by
setting "Catagory (X) axis labels" to A1:A7 does not change anything.)

See how the chart ignores the dates associated with 22, 44, and 66?
Worse yet... sort the data by the date so that it looks like this:

1/1/2004 1
1/1/2004 22
1/3/2004 3
1/4/2004 5
1/5/2004 44
1/7/2004 7
1/7/2004 66

The series get messed up because they refer to the same cell locations as
they did before the sort, so redefine the series by right-clickign on the
chart|Source Data|Series Tab -- You can use the control key to select
non-adjacent cells. So now Series1 once again refers to 1, 3, 5, and 7 and
Series 2 refers to 22, 44, and 66.

Check out the chart. It's ignoring the dates associated with BOTH series.
It lines up the first n items in Series1 and the first n items in Series 2
with the first n dates. That is actaully what it was doing all along, but it
was harder to see before the sort when the first n dates were appropriate for
the first n items in Series1.

Surely it would be more useful for a chart to match up items in series with
their associated labels which are a subset of axis labels than with the first
n axis labels, yes?

Does anyone have more ideas?
 
Thanks for sharing the example. A XY Scatter chart works just fine.
Note that it is different from a line chart.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

(a) Scatter charts seems to have the same problem. So far I've tried line,
column, bar, and area, as well to no avail.

(b) Yep, the x-axis already has its Type set to Time.

Thank you for the suggestions nonetheless.

Here's an example for anyone who wants to test it out:
(This would be the REALLY long version of my question.)

Let's assume the first date is in cell A1 and the first integer is in cell B1.

1/1/2004 1
1/3/2004 3
1/4/2004 5
1/7/2004 7
1/1/2004 22
1/5/2004 44
1/7/2004 66
Click to expand...
{snip}
 
Oooh, you're right -- a scatter chart does work! What didn't work was
converting from one of the other types to a scatter.
Thank you.

Unfortunately I can't do everything with scatter charts. Alas.

Anyone with more ideas?

-Nathan
 
Tushar -

Like all Line charts, the chart uses only the first series' categories for all the
series in that chart group. It doesn't matter that the categories are almost treated
as numerical values.

What can be done is to use the same dates for all series, with a blank cell if the
date has no Y value. Then under Tools > Options > Chart, the Interpolate Blanks
option should be selected.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
What can't you do with an XY Scatter chart? I'll bet someone has a workaround.

I gave a suggestion in my reply to Tushar's first response: list all the dates in
one column and use this as the X data source. In the columns with the series Y
values, put data only next to the dates that have values, and leave the rest blank.
Make your line chart, then from Tools > Options > Chart, choose Interpolate Blank Cells.

See, what happens is that a category axis (line chart, area chart, column chart, bar
chart) has fixed categories, defined by the first series. All subsequent series are
made to conform to these categories. A time scale axis seems to break free of the
category framework, but all it does is arrange the categories by date, but the third
point of any series plots above the third category, without any regard to the series
having independently specified categories of its own.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
What I can't do with an XY Scatter chart is have it be a Column chart :)
Other than that, yes, XY Scatter seems golden.

So as I understand your suggestion my data will plot right if I reorganize
it to look like this (and use the dates to define the x-axis, the first
column of integers as Series1, and the second colum of integers as Series2):

1/1/2004 1 22
1/3/2004 3
1/4/2004 5
1/5/2004 44
1/7/2004 7 66

I tried this and it works great with an XY Scatter and the other types of
charts as well! Wonderful to have a non-XY-Scatter chart display the data
accurately for a change :)

Unfortunately however, some people don't have authority to change their
layouts. I do in this case, but I'm sure I won't always.

In my case I can't reorganize my layout this way for another reason. In the
examples, your layout is marginally more complex than mine, but in real life
for my data your layout would be unmanageable as an entry point for data --
and I'm not faulting you here of course -- you have no way to know what my
real-life data looks like.

That said, inherent in the reorganization you suggested is a tendency toward
unmanagability for lots of folks. I'll try to explain why this is so for
anyone reading who has the same problem as me who doesn't see it...

A lot of spreadsheets keep track of events as they occur, such as
expenditures of funds or accumulation of whatever quantities.

When an event occurs, the date, an item that implies distinction between
series such as a person's name or the reason for the expenditure, and perhaps
5 quantities are entered, which means 7 columns in total for example.

Each time an even occurs, a new row is added. Most anyone looking at the
data can understand it quickly and enter the data in the correct cells,
probably with no need to scroll horizontally which, let's face it, confuses a
lot of people.

See how that layout corresponds nicely to the two columns in my initial
example?

DATE DATA
1/1/2004 1
1/3/2004 3
1/4/2004 5
1/7/2004 7
1/1/2004 22
1/5/2004 44
1/7/2004 66

Note that there is a third implied column in my example that differentiates
between the series. I took a shortcut and used single-digit data versus
double-digit data to distinguish between the series, but that isn't likely to
happen in reality.

So with that implied column spelled out, my simple initial example looks
like this:

DATE DATA SERIES_IDENTIFIER
1/1/2004 1 S1
1/3/2004 3 S1
1/4/2004 5 S1
1/7/2004 7 S1
1/1/2004 22 S2
1/5/2004 44 S2
1/7/2004 66 S2

(S1 is what makes the data belong in Series1, and S2 is... you get it.)
See how that looks a lot like my event-tracking example now? Just add 4 more
columns like the one titled DATA and it's a perfect match. Maybe we should
call the data columns D1, D2, D3, D4, and D5.

If the layout is changed to make columns distinguish between different
series like this:

DATE S1D1 S2D1
1/1/2004 1 22
1/3/2004 3
1/4/2004 5
1/5/2004 44
1/7/2004 7 66

(Column S1D1 contans the first quantities recorded for Series1 and column
S2D1 contains the first quantities recorded for Series2.)

....And then the data plots correctly.

Remember when the first simple example layout was fleshed out to match the
event-tracking example by adding 4 columns of data? To flesh out this new
layout, 8 columns of data must be added: S1D2, S2D2, S1D3, S2D3, S1D4, S2D4,
S1D5, and S2D5. So you see one dimension of potential unmanageability
inherent here. The other dimension is when more series are needed.

In the first layout if you need to plot 10 series, what do you do?

DATE DATA SERIES_IDENTIFIER
1/1/2004 1 S1
1/3/2004 3 S1
1/4/2004 5 S1
1/7/2004 7 S1
1/1/2004 22 S2
1/5/2004 44 S2
1/7/2004 66 S2

Record values like S3, S4, S5 ... S10 in the SERIES_IDENTIFIER column and
the total number of columns remains 3 (or remains 7 in the case of 5 data
columns).

In the second layout if you need to plot 10 series, what do you do?

DATE S1D1 S2D1
1/1/2004 1 22
1/3/2004 3
1/4/2004 5
1/5/2004 44
1/7/2004 7 66

Add columns like S3D1, S3D2, S4D1, S4D2... and the total number of columns
becomes 11 (or becomes 51 in the case of 5 data columns). Of course with all
the empty cells and longer column names, readabilty/manageability suffers.

So readers I'm sure can see that with few types of data and few series the
second layout with the columns doing double-duty keeping track of the data
and keeping track of series works fine (with the added bonus of the chart
actually plotting the data in a way that a human being would expect and
interpret as correct) but with more types of data and more series the layout
can get out of hand.

Anyway, what was I going to say?
Oh, yeah. Thanks very much Jon. I'm sure half of the future readers with my
problem will be able to use your suggested layout and leave happy.

I'm sure many times I'll be able to use that suggestion too, or Tushar's,
but alas, I won't be able to use it this time, so my question becomes:

Is there any way to get 3 series in sync with the x-axis using a
non-XY-Scatter chart without changing the basic layout of my data?

-Nathan
 
To make a long story short...

Your efforts to make the data entry system comprehensible to the user is admirable.
Of course, there's no rule that says the data entered must be plotted as is. For
uncomplicated data arrangements, simple worksheet formulas or defined names can make
the data plottable.

The data that you show is perfectly suited to analysis by pivot table.
DATE DATA SERIES_IDENTIFIER
1/1/2004 1 S1
1/3/2004 3 S1
1/4/2004 5 S1
1/7/2004 7 S1
1/1/2004 22 S2
1/5/2004 44 S2
1/7/2004 66 S2
Click to expand...

In fifteen seconds I turned the data above into the chart-ready table below:

Sum of DATA2 SERIES_IDENTIFIER
DATE S1 S2
1/1/2004 1 22
1/3/2004 3
1/4/2004 5
1/5/2004 44
1/7/2004 7 66

If you use a dynamic range to define the pivot table source data, refreshing the
table will capture the added data. A pivot chart will update automatically when the
pivot table is updated, but cannot render a time scale axis. A regular chart will
only update the number of points in a series using named ranges for series data, but
will not add series accordingly. So you should use a regular chart, and update it
with a macro after refreshing the pivot table.

Thanks to Debra Dalgleish, there is a section on my web site that can help you get
started with pivot tables:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

Debra adds to this on her own site. Look under P in the index:

http://contextures.com/tiptech.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
My temporary solution before reading your latest post was to use a slew of
named ranges with inelegant names like 2004eoq3c2 and to add a workbook sheet
whose sole purpose was gathering totals/averages based on the actual data
sheets.

I added rows marked "Do not edit" to contain the named ranges so that the
named ranges get moved properly when rows are inserted at the edges of the
ranges.

That sounds like your suggestion for "uncomplicated data arrangements".

Since my data needs to be sorted by date for one chart and sorted by another
item for a second chart, ranged names for both sort orders would gridlock the
data: Sorted one way, inserting a row would require the redefinition of all
named ranges for sorting the other way that happened to be down-page of the
inserted row.

Removing the simpler set of named ranges and its associated chart and
re-creating that chart each time it is needed removes the gridlock, but isn't
a permanent solution.

I had begun to look at pivot tables and charts as a possible permanent
solution but found that pivot tables appear to be static data summaries,
requiring their re-creation every time new data is entered.

Excel seems to have tried very hard to make pivot tables/charts easy and
user-friendly but not easy enough for my end users who will only be able to
enter rows of data -- nothing else. Fifteen seconds for you (and probably for
me once I figure it out) is certainly quick enough but my successors won't be
able to make use of this.

Using a "dynamic range to define the pivot table source data" and refreshing
sounds good... I think I can trust them to refresh rand run a macro... if I
write a tutorial.

I'll have to learn more about these things myself first -- I'll post again
when I get any results to speak of.

Thank you very much for the help and links.

-Nathan
 
Nathan -

You can carry the pivot table approach further. When the pivot table source data is
changed, the dynamic range that defines the data changes. You could use a
WorkBook_Change routine that detects the change and updates the pivot table.

Create a defined name that grows with the pivot table data range (Sheet1!PT_Source).
right click the Sheet1 tab, View Source from the pop up menu. This opens the
worksheet code module in the VB Editor. Choose Worksheet from the top left dropdown,
which creates the Worksheet_Change event procedure in the module. Insert a few lines
that determine whether the defined pivot table source range has changes, and if so,
updates pivot tables in other sheets:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("PT_Source")) Is Nothing Then
Worksheets("Sheet2").PivotTables("PivotTable1").PivotCache.Refresh
Worksheets("Sheet3").PivotTables("PivotTable1").PivotCache.Refresh
Worksheets("Sheet4").PivotTables("PivotTable1").PivotCache.Refresh
End If
End Sub

This updates pivot tables on sheets 2, 3, and 4. Any defined names that determine
what piece of each pivot table to chart should update with the pivot tables. All the
user has to do is enable macros when the workbook first opens.

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