Stacked Area Charts

[Airfix9]

Hi all,

I am creating a bonus template for our salesforce. It tracks how they are doing against 4 key criteria over the period of a quarter. What I would like to do, then, is to create a nice visual for the sales guys (oh look, a pretty picture!).

So, this is how I visualise it. Each of the four elements will be expressed as a percentage of the salesman's total salary. So consider the following data:

Book2
	A	B	C	D
1	TimePassed	0.00%	49.23%	100.00%
2	Crit1	0.00%	1.22%	#N/A
3	Crit2	0.00%	2.00%	#N/A
4	Crit3	0.00%	1.00%	#N/A
5	Crit4	0.00%	2.00%	#N/A
6	Max	0.00%	4.92%	10.00%
Sheet1

We can see above that the total amount of time passed in the quarter is 49.23%, so I need to plot the current position of each criteria along the X-axis accordingly. I don't, however want to plot the 100% value as this isn't known. The Max value, however, is a line from 0% to 10%, which is the maximum bonus available.

The way that I visualise the graph, then, is a stacked area chart with each criterion stacking on top of the previous one with the shading below until it reaches the line below. Alas, however, I can't seem to get this to work... chiefly because the lines rise up and then drop back down again (I thought =NA() was supposed to be unplottable). The idea is that, horizonally, the areas will colour until they get to the current position (49.23%) whereupon the colour will not show.

I have experimented with another set of data, commecing at the 49.23% level and projecting forward like this:

Book2
	A	B	C	D
1	TimePassed	0.00%	49.23%	100.00%
2	Crit1	#N/A	1.22%	2.47%
3	Crit2	#N/A	2.00%	4.06%
4	Crit3	#N/A	1.00%	2.03%
5	Crit4	#N/A	2.00%	4.06%
Sheet2

The issue here is that I can't seem to make the area transparent (no colour) without it covering up the real area graph.

This is driving me mad as I am sure that it should be possible and I am sure that it will look visually stunning when I manage to deal with it properly.

Where am I going wrong?

Any help gratefully received.

 

[Airfix9]

Re: Stacked Area Charts - Still need help

Hi all,

Not sure if I have confused... Here is a picture of what I am looking to achieve:



Any ideas, please?

 

[Gunswick]

dunno if you can do it with graphs. but for pictures, select the picture, then on the picture toolbar, click on "set transparent colour" to make white areas become see-through.

you may have to export your graph to paint, save it as a picture and then insert it into your sheet.

just record a macro and it should do it fine if you need to do that long process.

 

[Airfix9]

Alas, the file is interactive - the same file is used for each salesman and they punch in their name, which is checked against their NTLogin and, if it passes, the data is downloaded. This means that the graph is re-drawn each time!

This needs to be an on-board solution within the file itself.

 

[Gunswick]

so did you try the "set transparent colour" option at all? This is an on board solution if its works...

why cant you have a macro that runs to make the graph when a user logs in ?

 

[Airfix9]

The issue is what type of graph to create. The only version that comes (anywhere near) close is an Area graph but, because it can't plot the #N/A values, it creates this graph:



The alternative is to project the data forward to the end of the quarter, but I can't change the 3rd data point to colourless - you can only change the colour of the whole series.

Another idea is to create dummy data to project forward, but they don't follow on from the existing points, rather they stack on top.

My VBA skills are growing but they don't yet extend to graphing.

This is starting to get me down!

 

[Airfix9]

Re: Stacked Area Charts - Still not solved

Any suggestions please?

Sorry to bump.

 

[Gunswick]

could you create your graph, as per post 6.

Then just paste on top of the chart, a picture of a blank area of chart, so that it looks like your picture in post 2 ?

This is obviously bodging it a little, but it would look correct!

you could then use print screen and paint to make it into a picture, and insert it into your sheet as a clean looking chart - though it would not be dynamic per se.

you could record a macro whilst doing it which could be linked to a button. This would essentially manually update your chart.

you could even make it a private macro to that sheet, with the "with change" parameter. so whenever a value on your graph data sheet changes, it re runs the chart macro, so you can have a dynamic graph.

personally i would use a button, and just update the chart when i wanted it to be refreshed.

 

[Airfix9]

Sorry, this is (a) beyond my skills a little and (b) not within my self-imposed parameters of providing a professional service to my sales force.

Am beginning to suspect that this is not possible in a fully-automated way.

Thanks for your help anyway.

 

[Gunswick]

then you should a) learn... how can you expect a response that will be 100% easy peasy. What I suggested is actually very easy, you just press record macro, then copy paste a few things... then just press stop recording...

b) pressing a button to update a chart seems perfectly professional if it provides the correct printable output to your users...