Scatterplot with background area plot

[MikeM]

I’m plotting (Excel 2007) monthly crime data from 1960-2007 (576 data points)
for USA counties. The crime rate is the crime count divided by the
population. However, not every agency in a county reports every month, and in
some cases they don’t report for a number of years.

My plots, therefore, show four lines: total pop (black), reported pop
(green, semitransparent), and crime count x 100 (blue) are all on an
adjustable vertical axis, because counties vary considerably in population
and crime counts. The fourth plot, crime rate (crime count divided by
reported pop, orange), is on a fixed axis, so different counties’ rates can
be compared. I’ve uploaded two figures; they’re at
sociology.osu.edu/mdm/CountyPlots.pdf
To make these plots more readable, I’d like to plot the green line as a
solid area in the background. I’ve tried to use the “area†chart type for
that plot, but it plots the data as running from 1 to 576, instead of from
20454 (1/1/60) to 37985 (12/31/07).

How can I accomplish this, and in addition how can I make this plot stay in
the background?

TIA,
Mike

 

[MikeM]

I’m plotting (Excel 2007) monthly crime data from 1960-2007 (576 data points)
for USA counties. The crime rate is the crime count divided by the
population. However, not every agency in a county reports every month, and in
some cases they don’t report for a number of years.

My plots, therefore, show four lines: total pop (black), reported pop
(green, semitransparent), and crime count x 100 (blue) are all on an
adjustable vertical axis, because counties vary considerably in population
and crime counts. The fourth plot, crime rate (crime count divided by
reported pop, orange), is on a fixed axis, so different counties’ rates can
be compared. I’ve uploaded two figures; they’re at
sociology.osu.edu/mdm/CountyPlots.pdf
To make these plots more readable, I’d like to plot the green line as a
solid area in the background. I’ve tried to use the “area†chart type for
that plot, but it plots the data as running from 1 to 576, instead of from
20454 (1/1/60) to 37985 (12/31/07).

How can I accomplish this, and in addition how can I make this plot stay in
the background?

TIA,
Mike

 

[Shane Devenshire]

You need to switch the type of Axis from Category to Date. Right-click the
x-axis and choose Format Axis, on the Axis Options tab set Axis Type to Date
axis.

 

[MikeM]

This doesn't work. I need three of the plots to be scaterplots and the fourth
to be an area plot. I can't choose the axis type for just one of the plots,
in either Excel 2003 or 2007.

 

[MikeM]

Shane, I finally got to the correct frame, one that permitted me to select
"Date" as an option.

Thanks.

Mike

PS: I'm posting another question that perhaps you can help with.