comparing 2 values on one column in chart

[Guest]

I am working on a spreadsheet comparing the median sold $ to the average sold
$. I want to show this data in a column chart, the primary x axis is price,
the y axis is by month. Since I have 2 values for each month, I want to
display the data on one column per month, with the column color-coded and
"split" so the user can tell which is which. for example, the median sales
price in Dec 04 is $181,900, and the average sales price is $237,000. I would
like the lower value (the median price) first, with the higher value (the
average price) making up the different between $181,900 and $237,000. Right
now my chart is stacking both values, for a total of $418,900.

Does anyone know how to do this? I know it can be done because I am looking
at a hard copy that I am trying to duplicate. Any help would be greatly
appreciated.

Carly

 

[Jon Peltier]

Select the clustered column subtype of the column chart.

- Jon

 

[Guest]

Hi Jon

Thank you for your advice. However, I looked into your recommendation and I
don't think this is correct. This option simply stacks the 2 values on top of
the other, whereas I would like the column height value to be the greater of
the two values, not stacking the values. Any other suggestions would be
greatly appreciated. I am doing this for work and it is taking me too long!

Carly

 

[David Biddulph]

Regulars in this group will be amused at you telling Jon (of all people)
that he is wrong.

You need to read again what he said.
He said select the CLUSTERED column subtype of the column chart.

By the sounds of your symptoms, and how you have described them, you have
selected the STACKED column subtype.

Would you like to try again?

 

[Guest]

Ok David, I did as you and Jon advised. Now I have two columns of the data,
and I would like the data "stacked" on one column as described in my original
post (below). How do I go about doing this?

Again, thank you for your help.

Carly

 

[Guest]

One more thing, I am creating a chart that graphs the market listing activity
in AZ. I am creating an electronic copy from a hard copy. On the hard copy,
at the bottom of each column is the average number of days a house was listed

 

[David Biddulph]

Ah, now I see what you're after. You want a stacked chart, but for your
first series you want the median (if that's the lower value), and then for
the second series you want the difference between the two values.

If your category labels are in column A, your median values in column B, and
your average price in column C, then I suggest that you put the difference
in column D with the formula =C2-B2 (copied down as necessary). Hide column
C and plot columns B and D against column A.

 

[Guest]

David: Thanks so much for your response. Yes, I want the lower value for the
first series, then the second series on top. But, I need to display the
values for this data, and if the second series is mapped as the difference
the correct value won't be displayed.

 

[Jon Peltier]

Use one of these handy (& free) Excel add-ins:

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

They help you add labels from a worksheet range to a series of points. Apply
the original data for the upper series to the points with altered value (the
difference).

- Jon