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

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!

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.

<qq11ww22@eml.cc> wrote in message
news:1108332707.127932.311030@o13g2000cwo.googlegroups.com...
> 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!
>

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@eml.cc wrote:

> 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!
>

Hi,

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

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

Thanks much.

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/
_______

qq11ww22@eml.cc wrote:

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