Combination Chart Problem

[dee]

Hi,

I'm not sure if this is possible, but I would like to create a combination
chart the has one axis correspond to the other:

For argument's sake, let's say I buy veggies at a particular price and want
a chart to reflect the price and the amount purchased:

Qty in lbs. 100 300 200
Price .5 1 2

I want the chart to have a marker for each of the Qty values, but the second
axis displaying the Price values directly opposite the corresponding Qty
value.

Any help would be appreciated!

 

[Jon Peltier]

Do you have categories as well? Or would an XY chart, with Qty as X and
Price as Y suffice?

- Jon

 

[dee]

Yes, the column headings are dates (years).

--
Thanks!

Dee

Do you have categories as well? Or would an XY chart, with Qty as X and
Price as Y suffice?

- Jon

 

[Jon Peltier]

Create your chart, with series in rows, so that years appear on the
horizontal axis. I'd say a line chart is probably best. Double click one
series, and on the Axis tab, select Secondary.

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

Yes, the column headings are dates (years).

 

[Del Cotter]

Yes, the column headings are dates (years).

With two independent variables each against year, you need two lines,
one for price against year and one for quantity against year; then you
can use two y axes. Matching the axes so that you only use one line
would require the two variables to be linearly dependent, as for
instance when plotting temperature, when you can have an axis for
Centigrade and Fahrenheit.

You can still follow the XY chart solution, if you label the data points
with their year, either manually or using one of the free add-ins
available.

 

[dee]

Hi Jon,

Thanks for your help. I tried this, but it doesn't seem to do what I am
trying to do, unless I misunderstood you.

Let's say I have Jan, Feb, Mar, Apr, etc. as the column headings
Then I have, say row 2 that contains:
$10,000 $45,000 $50,000 $18,000

and row 3 that contains:
55 65 73 20

I would like one axis to show the amounts on row 2 - no problem there.

I would like the secondary axis to show the values on row 3, but... I want
them directly opposite the values. So, the $10,000 would have a tick
directly opposite it on the value axis that marks 55...

Thanks!

 

[Jon Peltier]

But then it's not an axis on the right, since the row 3 values are not
proportional to the row 2 values. For example, 18000 > 10000 (row 2, columns
1 and 4), but 20 < 55 (row 3, same columns). This is what Del pointed out in
his post.

It would be less confusing to add the row 3 values as data labels to the row
2 points. Use one of these free utilities to handle this:

Rob Bovey's Chart Labeler, http://appspro.com
John Walkenbach's Chart Tools, http://j-walk.com

- Jon

 

[dee]

Hi Jon,

For some reason, I never even saw Del's response until today.

I can arrange the data in any way, so that isn't a problem. Del mentioned
adding labels manually, which wouldn't be a problem, as I don't have many
items to chart. I'm not sure how to do this, though.

Thanks so much!

 

[Del Cotter]

I can arrange the data in any way, so that isn't a problem. Del mentioned
adding labels manually, which wouldn't be a problem, as I don't have many
items to chart. I'm not sure how to do this, though.

If you create some labels with one of the automatic options Excel
provides (value or category), you can then select the label series by
single-clicking on one of the labels. If you then carefully single-click
again on a label (not too fast; you don't want to double-click), a
single label out of the whole range is selected.

Now if you go up to the formula bar above the chart and spreadsheet, and
type in an equals sign followed by a cell reference, or type an equals
sign and then navigate to the cell you want that label to refer to, the
label will display the contents of that cell.

(if you find you've accidentally created a text box, that means you
hadn't selected one label: the text box will correctly display the cell
value, but won't move around properly to follow the series marker when
you change the graph)

Repeat for each label, and you've now got customized data labels that
can display anything, instead of only the value or category of the
series marker. Excel should long ago have included an option to select a
cell range to create all the labels at once (Lotus 123 had this back in
the 1980s!), but fortunately there are little add-ins distributed free
by friendly Excel experts if you have a heavy load of such labels.
Whether you want to do it manually or install one of the add-ins depends
on how much of this kind of thing you expect to do.

 

[dee]

Hi again - thanks for your detailed message. So, basically, if I understand
correctly, because my data is "scaleable", such as cel/far temperature, it
isn't possible to have markers that correspond on the two axis. The only way
to reflect the value for a certain amount is to add the labels as per your
instructions.

Just want to make sure I'm totally clear on this. Thanks!!

 

[Del Cotter]

Hi again - thanks for your detailed message. So, basically, if I understand
correctly, because my data is "scaleable", such as cel/far temperature, it
isn't possible to have markers that correspond on the two axis. The only way
to reflect the value for a certain amount is to add the labels as per your
instructions.

Basically, your data has two completely independent dimensions to it, so
it's got two degrees of freedom. In any year the data could have a
certain quantity, and then a certain price, and the quantity and the
price are free to be any value independently of each other. So if you're
going to represent year, quantity and price all in one line on a
two-dimensional surface, you've got to make the most of the two
dimensions by letting one of them be the quantity, and the other be the
price, in a graph of *orthogonal coordinates* (orthogonal being a fancy
word for "at right angles to each other".

(Temperature doesn't have this problem because every centigrade
temperature forces a fahrenheit temperature, and vice versa: it's only
got one degree of freedom, leaving you with a spare dimension to display
your years in)

If you want price and quantity to be in *parallel coordinates*, you'll
need to have two lines, one of them quantity against year, and the other
one price against year. If you want price and quantity in one line, you
have to drop the year (or somehow twist the line into a third
dimension). Labelling the point with their year is still possible
though, so that's good.

So you have two graphs to choose from, an Excel Line Chart type with two
lines, and an Excel XY (Scatter) Chart type with one line, neatly
labelled so you can see the years. Why not try both, and see which your
audience prefers. Sometimes it's good to show the same data two
different ways, as it brings out different features.