Offsetting Chart data

[talcummalcolm]

I require to view data in chart format, the problem is as the data lines
are so close I need to offset the data to view all on one chart for
comparison.
Can anyone help me with this??

 

[Tushar Mehta]

What does that mean? "Offset the data?" If you want to see only a
subset of the data, see the Dynamic Charts page of my site. You should
be able to adapt one of the examples to your needs.

--
Regards,

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

 

[dvt]

I require to view data in chart format, the problem is as the data lines
are so close I need to offset the data to view all on one chart for
comparison.
Can anyone help me with this??

I'm going to make a different guess than Tushar. I suspect you could use
a broken axis for your purpose. In your case, your axis might span 1-3 in
the first part, then 1-3 again in the last section. A broken axis is
normally used to display values that are very different, but there's no
law that states this must be so.

To learn how to make a broken axis, see Tushar's site
(www.tushar-mehta.com). Look for Broken Y Axis on the left. Or try Jon's
site (http://www.geocities.com/jonpeltier/Excel/Charts/axes.html#Broken)..

How many data lines do you have?

 

[Jon Peltier]

I think Malcolm wants to move one series slightly to one side to make it
easier to see next to the other.

An approach I sometimes use is as follows. Put the data into a
worksheet range, say A5:C10, where B5 and C5 are the cells containing
the series names, and column A are the X values. Make the chart with
this data.

In cell D4 put a small offset value, say -0.1, and in cell E4 put
another, say 0.1 (the actual values depend on what you have and what you
want). In cell D6 I put the formula =$A6+D$4. I drag this to fill E6,
then I drag both to fill D6:E10.

Right click the chart, choose Source Data from the pop up menu, and
click on the Series tab. Select the first series, and change the X
Values range from A6:A10 to D610. Select the second series, and
change the X Values range from A6:A10 to E6:E10.

If your offsets aren't right, you can adjust the values in D4 and E4 and
they will all adjust. This example moves one series left slightly and
the other right slightly. If you have multiple series, you may want to
leave the first alone (offset = 0), then move each additional series by
a higher multiple of the offset. Put the offset in D3, then in D4 put
=0*$D$3, in E4 put =1*$D$3, in F4 put =2*$D$3. or use a formula like
=(COLUMN()-COLUMN($D$3))*$D$3 in D4, and drag it to fill as far to the
right as you need.

Sure, it's a kludge, but the full extent of Excel's charting
capabilities are only available through such workarounds.

- Jon

 

[talcummalcolm]

*On Thu, 6 Nov 2003 04:15:11 -0500, talcummalcolm =



I'm going to make a different guess than Tushar. I suspect you could
us=
e =

a broken axis for your purpose. In your case, your axis might span
1-3 =
in =

the first part, then 1-3 again in the last section. A broken axis is
=

normally used to display values that are very different, but there's
no =

law that states this must be so.

To learn how to make a broken axis, see Tushar's site =

(www.tushar-mehta.com). Look for Broken Y Axis on the left. Or try
Jon=
's =

site
(http://www.geocities.com/jonpeltier/Excel/Charts/axes.html#Broken)=
..

How many data lines do you have?

-- =

Dave
dvt at psu dot edu *

Thanks for that, Jons page holds the key think...Thanks all for your
help - TM

 

[talcummalcolm]

*I think Malcolm wants to move one series slightly to one side to
make it
easier to see next to the other.

An approach I sometimes use is as follows. Put the data into a
worksheet range, say A5:C10, where B5 and C5 are the cells
containing
the series names, and column A are the X values. Make the chart
with
this data.

In cell D4 put a small offset value, say -0.1, and in cell E4 put
another, say 0.1 (the actual values depend on what you have and what
you
want). In cell D6 I put the formula =$A6+D$4. I drag this to fill
E6,
then I drag both to fill D6:E10.

Right click the chart, choose Source Data from the pop up menu, and
click on the Series tab. Select the first series, and change the X
Values range from A6:A10 to D610. Select the second series, and
change the X Values range from A6:A10 to E6:E10.

If your offsets aren't right, you can adjust the values in D4 and E4
and
they will all adjust. This example moves one series left slightly
and
the other right slightly. If you have multiple series, you may want
to
leave the first alone (offset = 0), then move each additional series
by
a higher multiple of the offset. Put the offset in D3, then in D4
put
=0*$D$3, in E4 put =1*$D$3, in F4 put =2*$D$3. or use a formula
like
=(COLUMN()-COLUMN($D$3))*$D$3 in D4, and drag it to fill as far to
the
right as you need.

Sure, it's a kludge, but the full extent of Excel's charting
capabilities are only available through such workarounds.

- Jon

Cheers Jon, Great and helpful web page - TM