Age in X-axis Labels as "YY;MM" ??

[qq11ww22]

Hi All,

I cannot, for the life of me, figure out how to get excel to chart some
data of in the format of year;month to represent an age. This is a
common convention in the humanities, but I can't make excel do it.

Bascially, I want to plot data like this on an X-Y scatter plot

1;8 40
1;9 50
1;10 40
1;11 33
1;12 44
2;00 21
2;12 64

Thanks for any help you could give!

 

[Barb Reinhardt]

I'm not following what you want for your X coordinates. Is 1;8 the same as
August 2001? If so, EXCEL doesn't know that. You'll need to enter the
month/year in a format that EXCEL knows before you can go further.

 

[Jon Peltier]

This might do it. Instead of what Excel considers meaningless text (no
humanities jokes!), enter your ages as fractional years. 1;8 means 1
year + 8 months, or 1 8/12 years (1.6666667). Format the column with a
custom number format of

# 0/12

and your column of ages looks like this:

1 8/12
1 9/12
1 10/12
1 11/12
2 0/12
2 0/12
3 0/12

Notice that two of your ages are converted to whole years (0/12) from an
age of Y + 12 months. In fact, a custom number format of

#";"0/12

retains the semicolon:

1;8/12
1;9/12
1;10/12
1;11/12
2;0/12
2;0/12
3;0/12

though you are stuck with the /12 if you want the number to remain
numeric. Just use this as your age variable and the last column as the
value to plot against it.

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

 

[qq11ww22]

Hi,

Actually 1;8 is supposed to mean 1 year, 8 months. So it is a time
interval, rather than a date.

 

[qq11ww22]

Ah, nice trick. Not ideal, but probably as good as I will get without
some serious mojo.

Thanks much.

 

[Jon Peltier]

qq -

Yes, and a date is merely the elapsed time from some reference date.
Just a technicality.

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