Time Series Chart Data Plot Line ''Disappears'' When Y Axis Formating Is Changed

[John Burke]

SITUATION: I've never used Exel for chart-creation. This question
probably makes that all too obvious.

Using Exel 2002's chart wizard, I was able to generate a time-series
chart which displayed my data. However, on the resulting chart, the Y
Axis displayed the years generically (1-30). I wanted this axis to
display the years specific to the dates involved (e.g. 1920-1947).

I right-clicked on the Y Axis and choose <Format Axis>, followed by
the Scale Tab. There I set "Minimum:" to the starting data of my time
series, "Maximum:" to the ending date. All other values were kept the
same. I then clicked OK. The chart was now formated just the way I
wanted it.

However...

PROBLEM: The data plot line on the newly-formated chart is invisible
-- it "disappeared".

QUESTION: How do I get the chart to display **both** the time-series
data on the Y-Axis by date and the resulting data plot?

COMPLAINT: This **really** shouldn't be this
difficult!!!!!!!!!!!!!!!!!!!!!

Thanks,

2privatus

 

[Jon Peltier]

Your axis now goes 1920 to 1947, but the data is still plotted from 1 to 30.

Reset the axis, then go to the Chart menu > Source Data > Series tab. Click
in the Category (X Axis) Labels box if it's a Line chart, or in the X Values
box if it's an XY chart, and select the range of cells that contains the
years.

You should probably be using an XY chart for this.

- Jon

 

[John Burke]

Jon,

Thank you for your reply. However, your suggestion wasn't helpful.

I am using XY charts. In my source data, the years are already
identified as specific dates (1920-1947), not as generic numbers
(1-28). The Chart Wizard provides me with a graph that plots the co-
ordinates correctly, but the X axis is shown as generic numbers. It
seems that the year dates I've ascribed to the X axis are simply being
ignored in favor of the generic identifiers (1-28). Indeed, the Chart
Wizard chart "rounds up" to 30 on the X axis before it stops.

Now let me clarify what I think (?) the problem may be...

In <Format Axis>, there is a check box for "Value (Y) Crosses At",
whereupon it asks you to submit a value. When the chart is configured
so as the years are labeled generically (1-28), the Y Value and the X
Value can both be easily made the same - Zero (0). However, when I
want to use yearly dates (starting at 1920), then the X value is 1920,
while the Y value is still Zero (0) -- And the graph plotting my co-
ordinates "disappears".

I've even tried setting Value Y's "cross point / origin" at 1,920, in
the hope that this would "trick" the chart into displaying both the
graph **and** the yearly values for X. This (of course!!!) didn't
work.

Apparently (?), the values for X and Y at the origin must be made to
Zero (0). This means that somehow (???) the Exel chart to start at
Zero (0) and "leap" to my preferred starting date (1920). Or anyway,
that's my best guess as to what's going on.

(BTW, In most time series graphs, this is done with a little zig-zag
line (~~) between the Zero (0) origin point of the Y axis and the
starting date (1920 or whenever) plotted on the X axis where the data
series actually begins.)

I'm just trying to put together a time-series chart with the dates for
the data shown on the X axis. This is among the simplest, most basic,
graphs imaginable. Surely Exel charts can handle this (and why can't
they do so without all this hassle?!!!!!?)?

Here's hoping you, or anyone else you reads this, can help,

2privatus

 

[Jon Peltier]

You can't trick a chart in the way you're trying. I make my living tricking
Excel charts in other ways, but you can't make an axis show something that's
not there.

What does the series formula look like? (Select the series and look in the
formula bar.) It should look something like this:

=SERIES(Sheet1!$F$4,Sheet1!$E$5:$E$25,Sheet1!$F$5:$F$25,1)

Where Sheet1!$F$4 is the cell containing the series name (it could be a
literal string like "Sales" or empty), Sheet1!$E$5:$E$25 is the range
containing the X values (it could also be empty, in which case Excel will
use counting numbers 1, 2, 3 instead), Sheet1!$F$5:$F$25 is the range
containing the Y values, and 1 is the order this series is plotted among all
the series in the chart.

If the X range matches what the series formula contains for X values, then
check that the X values are numeric. If the years are stored as text not
numbers, or if you have any text in the range, Excel treats them as
nonnumeric values and again uses counting numbers 1, 2, 3 to plot the
points.

- Jon

 

[Jerry W. Lewis]

Excel dates are a format applied to the number of days since 1900. Therefore
numeric values of 1920 to 1947 correspond to dates in April of 1905, whereas
dates between 1920 and 1947 correspond to numeric values between 7306 and
17532.

You can use the DATEVALUE fucntion to contruct an Excel date from its text
representation. You can directly enter dates in the min and max axis values
fields for a chart.

Jerry

 

[John Burke]

Jon,

I apparently don't know how to "Select the series"? Please explain.

I, of course, tried to select the cells for the relevant X and Y
values. However, this did not give anything like the formula you
descrbed.

I continue to be astounded that something that should be so simple is
seemingly so complicated and hugely time-wasting.

2privatus

 

[John Burke]

Jerry,

Thank you for your reply.

What I'm trying to do is have the X axis of a annual time series chart
labeled by the particular **years** that correspond to the data.

How can I use DATEVALUE to achieve this?

From what you've described, it seems like DATEVALUE would only ascribe

a date to a particular **day**.

BTW, If it seems like I haven't fully investigated this matter, that
is correct. I've pursued many dead ends -- and wasted huge amounts of
time -- in order to try to accomplish something that **should** be so
simple. At this point, I'm relucant to expend more effort until I'm
reasonably sure it will not be in vain.

2privatus

 

[Jon Peltier]

If your data is in integral number of years, you don't need to follow
Jerry's approach. His treatment of dates is absolutely correct, but more
than you need if you are just tracking annual numbers.

Put years in one column, values in the next column. Leave a blank cell above
the list of years, and put a label above the list of values. Select this
range, including the row with a blank cell and the label, and create an XY
chart.

- Jon

 

[Jon Peltier]

I apparently don't know how to "Select the series"? Please explain.

Click on the plotted series in the chart.

I, of course, tried to select the cells for the relevant X and Y
values. However, this did not give anything like the formula you
described.

In the Chart menu > Source Data > Series dialog?

I continue to be astounded that something that should be so simple is
seemingly so complicated and hugely time-wasting.

Often it's made more complicated by a user having data formatted nicely for
a tabular report but not arranged efficiently for a chart. See my follow up
to Jerry's post to see an efficient data layout for a chart.

- Jon