[SOLVED] Missing lines in chart w/x-axis with months 1-24...

[DendWrite]

First post from a "casual" Excel user (more familiar with SigmaPlot)...

Client wants a line chart with x-axis linear (baeline to 36 months) but
there are only data for 2 baseline conditions and certain months:
on-drug
off-drug
M3
M6
M12
M24
M36

The Y axis (# of pellets) is nothing special.

I entered blank rows, corresponding to the missing months, into the
datasheet, like so:
on-drug
off-drug


M3


M6





M12 (etc.)

This "method" plots the data points okay, but there are 2 problems:
(a) there are no lines connecting them, and
(b) the x-axis labels are lousy

I worked around "b" by manually adding text boxes instead of x-axis labels,
but the missing lines problem is a major pain (I manually added lines, but
sometimes they don't hit the data points on center, and any resizing is a
bear!).

Is there a better way for Excel to deal with missing data and draw the lines
automatically, as it does if there are no empty rows in my datasheet?

Any help appreciated. Sorry for the length, but I figured it's better to be
specific.

Thanx

[Andy Pope]

Hi,

In order to make things easier for the chart wizard to guess your data
layout try adding a little more information to your data when you create
the chart.

B1: =# Pellets
A2: =""
A3: =""
A4: =M3
B4: =1 ' replace 1 with real data
A7: =M6
B7: =1 ' replace 1 with real data
A13: =M12
B13: =1 ' replace 1 with real data
A25: =M24
B25: =1 ' replace 1 with real data
A37: =M36
B37: =1 ' replace 1 with real data

Leave the cells not referenced blank.
Now select the range A1:B37 and create a Line chart using the chart
wizard. With the chart selected use the menu Tools > Options. On the
chart tab make sure the 'Plot empty cells as: Interpolated' is checked.

You may need to alter the 'Number of Categories between tickmarks' to 1
in order to see the M labels. Double click the x axis and go to the
Scale tab to change this value.

Cheers
Andy


DendWrite wrote:
> First post from a "casual" Excel user (more familiar with SigmaPlot)...
>
> Client wants a line chart with x-axis linear (baeline to 36 months) but
> there are only data for 2 baseline conditions and certain months:
> on-drug
> off-drug
> M3
> M6
> M12
> M24
> M36
>
> The Y axis (# of pellets) is nothing special.
>
> I entered blank rows, corresponding to the missing months, into the
> datasheet, like so:
> on-drug
> off-drug
>
>
> M3
>
>
> M6
>
>
>
>
>
> M12 (etc.)
>
> This "method" plots the data points okay, but there are 2 problems:
> (a) there are no lines connecting them, and
> (b) the x-axis labels are lousy
>
> I worked around "b" by manually adding text boxes instead of x-axis labels,
> but the missing lines problem is a major pain (I manually added lines, but
> sometimes they don't hit the data points on center, and any resizing is a
> bear!).
>
> Is there a better way for Excel to deal with missing data and draw the lines
> automatically, as it does if there are no empty rows in my datasheet?
>
> Any help appreciated. Sorry for the length, but I figured it's better to be
> specific.
>
> Thanx

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

[DendWrite]

Andy,

(Hey na, hey na...) The lines are back! That works quite nicely (even on my
existing graph).

Thanks very much,
Mike



"Andy Pope" wrote:

> Hi,
>
> In order to make things easier for the chart wizard to guess your data
> layout try adding a little more information to your data when you create
> the chart.
>
> B1: =# Pellets
> A2: =""
> A3: =""
> A4: =M3
> B4: =1 ' replace 1 with real data
> A7: =M6
> B7: =1 ' replace 1 with real data
> A13: =M12
> B13: =1 ' replace 1 with real data
> A25: =M24
> B25: =1 ' replace 1 with real data
> A37: =M36
> B37: =1 ' replace 1 with real data
>
> Leave the cells not referenced blank.
> Now select the range A1:B37 and create a Line chart using the chart
> wizard. With the chart selected use the menu Tools > Options. On the
> chart tab make sure the 'Plot empty cells as: Interpolated' is checked.
>
> You may need to alter the 'Number of Categories between tickmarks' to 1
> in order to see the M labels. Double click the x axis and go to the
> Scale tab to change this value.
>
> Cheers
> Andy
>
>
> DendWrite wrote:
> > First post from a "casual" Excel user (more familiar with SigmaPlot)...
> >
> > Client wants a line chart with x-axis linear (baeline to 36 months) but
> > there are only data for 2 baseline conditions and certain months:
> > on-drug
> > off-drug
> > M3
> > M6
> > M12
> > M24
> > M36
> >
> > The Y axis (# of pellets) is nothing special.
> >
> > I entered blank rows, corresponding to the missing months, into the
> > datasheet, like so:
> > on-drug
> > off-drug
> >
> >
> > M3
> >
> >
> > M6
> >
> >
> >
> >
> >
> > M12 (etc.)
> >
> > This "method" plots the data points okay, but there are 2 problems:
> > (a) there are no lines connecting them, and
> > (b) the x-axis labels are lousy
> >
> > I worked around "b" by manually adding text boxes instead of x-axis labels,
> > but the missing lines problem is a major pain (I manually added lines, but
> > sometimes they don't hit the data points on center, and any resizing is a
> > bear!).
> >
> > Is there a better way for Excel to deal with missing data and draw the lines
> > automatically, as it does if there are no empty rows in my datasheet?
> >
> > Any help appreciated. Sorry for the length, but I figured it's better to be
> > specific.
> >
> > Thanx
>
> --
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
>