Broken/Offset X-axis on a time-scale

[Lakshmi]

Hi,

I am trying draw line charts with a broken/offset x-axis
with the x-axis being on a time-scale.

I am mainly trying to show time kinetics of immune data at
first few time points (2, 5, 9 and 12 weeks) and then
split the graph and show data for same individual at 1, 2,
and 3 years down the line.

One way I am trying to do this is by changing the dates
for the week time points to months so that that portion of
the graph is stretched out and then manually labelling the
time points as weeks and years. But the graph dosent look
too good.

Could you let me know if there is a better way to do this?

Thank you,
Lakshmi

 

[Bernard Liengme]

I think you should use two data series: the short-range and the long-range.
Definitely two x-axis and possibly 2 y-axis.
Bernard

 

[Jean Ruch]

Hi,

I am trying draw line charts with a broken/offset x-axis
with the x-axis being on a time-scale.

I am mainly trying to show time kinetics of immune data at
first few time points (2, 5, 9 and 12 weeks) and then
split the graph and show data for same individual at 1, 2,
and 3 years down the line.

One way I am trying to do this is by changing the dates
for the week time points to months so that that portion of
the graph is stretched out and then manually labelling the
time points as weeks and years. But the graph dosent look
too good.

Could you let me know if there is a better way to do this?

Thank you,
Lakshmi

Hi Lakshmi,



As an alternative, you may use a logarithmic scale.

Not that one Excel proposes to you as an option in scaling, because you
can scale it only by intervals corresponding to exact decades, but
doing the following:

You take the number of days from the point zero to 3 years later, i.e.
in your case approximately after 0, 14, 35, 63, 84, 379, 744, 1109
days

and take the logarithm of these figures as values for your x-Axis

On the graph you let disappear these numbers, choosing for them

font-color = white and the attribute "not visible"

Label now your points with the corresponding dates using the Chart
Labeler

(free ad-in at http://appspro.com )



Have you also thought using for these short term / long term ranges a
second Y-Axis together with a second X-Axis ?



regards

Jean

 

[Guest]

Hi Jean,
Thanks for your suggestions. I tried it but I still have
problems.

I tried having the X-axias on a log scale as u suggested
but It dosent look good because the maximum point on the
scale is 10 and the maximum point I have data for is 3.

I cant understand how having a secondary Y-axis would
help. The data I have both at short range time point and
long range time point is within the same range on Y-axis.

I am not able to find out how to add a secondary X-axis?

Can you help me out with this.
Thanks,
Lakshmi

 

[Jean Ruch]

Hi Jean,
Thanks for your suggestions. I tried it but I still have
problems.

I tried having the X-axias on a log scale as u suggested
but It dosent look good because the maximum point on the
scale is 10 and the maximum point I have data for is 3.

I cant understand how having a secondary Y-axis would
help. The data I have both at short range time point and
long range time point is within the same range on Y-axis.

I am not able to find out how to add a secondary X-axis?

Can you help me out with this.
Thanks,
Lakshmi

Lakshmi,

It dosent look good because the maximum point on the
scale is 10 and the maximum point I have data for is 3.

Am'I wrong when assuming you took nevertheless the option "logarithmic
scale" as Excel sees it for?

What i meant was as follows.
You convert all your time datas (for which you have y-values) in days:
14, 35, 63, 84, 379, 744, 1109
With the Function LN() you take their Log, .ie.
2,64 / 3,56 / 4,14 / 4,43 / 5,94 / 6,61 / 7,01
These numbers will be your x-values with the advantage that on a
logarithmic scale you can this time scale your graph. Let us say
between 2,5 and 7,5
As these numbers are already the logarithms of your time Datas you
don't need anymore the logarithmic conversion of XL. You plot them in a
standard x-y Diagram as normal numerals.

Concerning a secondary X-Axis:

First you NEED TO create a secondary Y-Axis. To do this, the best is,
you have two identical columns of your y-values and make the Diagram
with both of them.
Once done, a doubleclick on the series you want to display on the
secondary Y-Axis. In the window which opens: Register Axis / select
secondary Axis.
This is a necessary preliminary to access to a second x-Axis.
Now right click in a void area in the middle of the Diagram : select
"Diagram options" / Axis /
select secondary X-Axis
This Axis will appear on the top of the Diagram, but you may place it
elsewhere (downwards) with the usual procedure.
You also may scale it as you want in the usual way.
I suggest you delete by way of trial the higher values (years) in the
first column and the lower values ( weeks) in the second column of your
Y-values
Both primary and secondary Y_Axis should be scaled identically.
It's up to you to find out if there is a scaling combination giving a
satisfactory
result.
For that, you must have the y-values in sight....
You may hide parts of the scaling of the respective Axis which would
disturb
with a white / opaque rectangle without borders.
May be giving the same color to your points and the corresponding
Axis could be an additive option.
A good Diagram is such one where you catch at a first glance what is
represented.

As I don't have an English version of Excel, my advices are not
necesseraly in an absolutely correspondance with the labeling you are
used to.....
Neverthelss I hope you may come clear with

cordially

Jean

 

[Jon Peltier]

Hi Lakshmi -

If you don't want to use a log axis or separate series, you could try my
broken Y axis example, but of course, adjusted to show the break on the
X axis:

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

- Jon

 

[Tushar Mehta]

If you want to use a log scale, you can simulate any maximum value.
For a tutorial see the Excel | Tutorials | 'Flexible Log Scale' page of
my web site.

Alternatively, you can adapt the ideas behind the 'Broken Y-Axis' page
of my web site (or the similar concept that Jon Peltier referenced on
his site), but adapt it to the x-axis. Basically, take your data and
plot them for say x=2,5,10,12, and use fake x values for the annual
data of, say, x=21, 22, and 23.

Add another series with the same x values (plus one more at x=16) and
all y values set to zero. Plot this to show a line and the marker that
has a vertical line in it. In some column put the real x values for
all the 7 real points; for the dummy point (x=16) enter 'Weeks\Years'
(w/o the quotes). Now, use Rob Bovey's XY Chartlabeler
(www.appspro.com) to label the dummy series you added (all y=0) with
the data in this newest column. Position the labels to be below the
marker.

For the actual x-axis remove the tick marks, the labels, and the line.

[For detailed instructions on any or all of the steps see the tutorials
referenced above.]

--
Regards,

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