PDA

View Full Version : Solved: How to draw following plot is excel?



arrun
04-28-2009, 11:13 PM
Hi all,

I have following dataset :

Item 01 Item 02
--------------------------------------------------
Ch 1 60% 20%
Ch 2 40% 80%
---------------------------------------------------
$100 $120


Now I would like to draw a plot like this : http://www.youshare.com/Guest/387e1f21d6a201d7.GIF.html. Here Percentage figures are drawn in primary axis and Dollar figure i.e. $100 and #200 is in secondary axis (in chart they are in deep blue color ploted horizontally). Can anyone please advise me whether it is possible in Excel? and if possible how?

Thanks
:help

Andy Pope
04-29-2009, 02:09 AM
It's possible but you haven't given enough imformation to make the chart meaningful.

To get the datum lines use xy-scatter chart with error bars.

Create a stacked column chart on all 3 series.
Move the Datum series to the secondary axis
change chart type to xy-scatter
Add X-error bars +/- 0.25
Format error bars to remove cap and increase thickness
format series to remove data marker

But you need to provide information on how $100 should align with the percentage values. At the moment there is no relationship.

Benzadeus
04-29-2009, 10:11 AM
Move the Datum series to the secondary axis
change chart type to xy-scatter


I'm trying to reproduce it... could you explain those two steps better?

Andy Pope
04-30-2009, 06:16 AM
See attached with step by step charts.

"Move the Datum series to the secondary axis"
- select the Datum Series
- CTRL+1 to display format dialog
- Axis tab choose Secondary Axis


"change chart type to xy-scatter"
- select Datum series
- right click and choose Change chart type

arrun
04-30-2009, 06:32 AM
Thank you so much Andy for your solution. It is really astonishing. Perhaps you have asked about the context. This is it : There is two items made from 2 compositions each. For item01, 1st composition consists of 60% and 2nd 40% wherein price of whole item is $100. Similarly for 2nd item. Here I wanted to plot percentage contribution of each composition and overall price for both items in a single plot.

Andy Pope
04-30-2009, 06:44 AM
What I don't understand though is what information the horizontal lines are giving you. Other than 1 is different from the other.

To my mind you just need to show the cost of each of the stacks.

In this example I have added a third series, moved it to the secondary axis and change the chart type to line
Use the Source data dialog to define the category labels for the secondary axis.

Now display Category data labels, positioned above, for the line series.