Excel2000: Need help creating stacked bar chart to display percentages

[chriscboy]

Hi,

I am trying to create a stacked bar chart that will show the value of products sold YTD, compared to the target

value of the products for the year. The data is laid out as below:

Actual (£) Target (£) Actual %age sold
Product1 3420000 3420000 100
Product2 800000 690000 116
Product3 50000 150000 33
Product4 80000 270000 30
Product5 20000 125000 16

I want each row to show the target value for the year, and the actual value for the year, with the X axis

diplaying as a percentage, and y axis showing the product. So the target value will be 100%, and the actual value will be displayed as a percentage of the actual.

The chart should also cater for when a product sold exceeds its target value (in the case of Product4).

So far I have managed to create most of this using a 100% stacked bar, but it doesn't seem to display the bars correctly. If you look at the attached example you will see that the actual value of Product1 is displayed as 50%, yet it should be 100%.

Please can you advise on how I can improve the graph so that it does display the bars correctly?

Thanks

 

[GlennUK]

First question ... why are you using a stacked bar chart? Stacking is usually used to show the components that go toward a total, and not to compare values.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[chriscboy]

Yes I am, but I don't think that it is achieving what I am looking for. I have now changed the data as follows:

Actual (£) Target (£) Percent Total
Product1 3420000 3420000 100 100
Product2 800000 690,000 116 100
Product3 50000 150000 33 100
Product4 80000 270000 30 100
Product5 70000 125000 56 100

I am now using a clustered bar chart using the percent and total columns which has the visual representation I am looking for. However I would like to display the actual and target values on the bars themseleves. Is this possible?