This is one of those problems where I feel that what I want to achieve is simple but to date it has stymied me.
I have an Excel chart that plots forecasts versus the sum of commitment and expenditure for each month of the year as a clustered column chart – that is there are 12 columns for the forecasts and 12 columns for the sum of the commitment and the expenditure.
I would like to change this to a combination column chart that still has 12 columns showing the forecast but the other 12 columns are stacked columns showing commitment and expenditure. (As commitments turn into expenditure the chart turns into a chart of forecasts vs expenditure.) Excel doesn’t want me to make this change and so far Excel is winning the battle!
Excel is happy for me to make up a combination chart, so long as I show the forecasts as a line graph, not columns – but that doesn’t make logical sense in terms of the data I want to display.
The current version of the chart does not rely on any VBA programming. I know that there is a way of achieving the chart that I would like by spending $45 on add-in software, which (a) seems excessively expensive and (b) can’t be introduced into my workplace’s computing environment anyway.
So, can anyone point me in the right direction for finding a solution to my problem? I am happy to consider solutions that involve VBA and those that do not.
I probably don't know the answer, but let me at least see
if I understand the issue
you have: Clustered Column chart
- series 1 = forecasts
- series 2 = sum of: commitment + expenditure
you'd like: "Combo Clustered Column" (which does not exist)
- series 1 = forecasts
- series 2 = Stacked Column (showing: commitment, expenditure)
OK.. now that I reread your post, that is exactly what you said !!
My guess is that your best hope is, as you are ready to deal with, VBA
General thinking:
1. Place an Image Control on the sheet
2. Create the columns using Line statement (can be rectangles filled with color, too)
This way you'll have control of the constituents of a given "column"
- series 1 is just forecast values
- series 2 overall height is sum of commitment+expenditure, but it can
be in 2 colors, lower region of 1 color (commitment), upper region of
2nd color (expenditure).
- the values would be extracted, using a loop, from cells on your sheet.
I've not done this, but it seems doable.
Can you take it from here?
can you do this manually in exel?
if so try recording a macro to generate some sample code
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
The problem is that I can't do this using Excel - it won't let me. However, I am about to try recording a macro where I mix stacked column and line, and I will then try to modify the macro to combine stacked and clustered. If I can get it to work I'll let you know the results & how to do it. (I can't be the ONLY person who wants to do this!)
OK, I give ... how did you do that ??
(btw, way cool )
As for "brute force", I meant creating some subs in VBA, and
writing the code. I started that in an effort to anticipate
Dragon's next step (ie, that no solution was possible), but
I didn't get very far. Piece of cake with VB6 (using PictureBox),
but I got nowhere in VBA using Image control.
Wow! (I bow before you, oh mighty Excel Superguru.) That's exactly what I want. But what Spoo & I would love to know is: HOW DID YOU DO THAT??? What are those tricks/cheating you refer to?
The previous trick is hard and it's ... hard for me to explain how it works. That keeps the data table format unchanged but mimic the graph to give the final result as we need.
This is another trick that is easier: reformat the data table as seen then create a normal stacked bar graph but it looks like a combination of cluster and stack bars.
This trick also can be used to create a multiple group stacked bars graph.
You should play with it then you may find a solution yourself. I don't want to give away this trick too easy before you contribute some works in it (so it might worths for you with $45 x many users and no need any add-in.)
Noted that there are a blank row and many blank columns on the data table.
.
Last edited by anhn; Apr 7th, 2010 at 08:03 AM.
Don't forget to use [CODE]your code here[/CODE] when posting code
If your question was answered please use Thread Tools to mark your thread [RESOLVED]
I think I see what you are doing. You've actually created two stacked charts, one of which has a row of null entries and you've then superimposed a second stacked chart, and used the column offsets to enable the superimposition. You use copy (the second set of data) and paste (onto the chart) to achieve the superimposition. I'll try playing around with that. When I get it working, I'll let you know.
No! That is only a single chart. If use 2 charts on top of each other you will have a problem with 2 different AutoMaximum Y values and not easy to align them.
Any way, you had a good thought about it.
I presented the table in horizontal layout so you can easily see that each month has 4 columns but the first and last columns are blank that matches with what you see on the graph.
Don't forget to use [CODE]your code here[/CODE] when posting code
If your question was answered please use Thread Tools to mark your thread [RESOLVED]
Oh? I've not run into problems with the two different automaximum Y values - possibly because we've spent the year shaking sticks at people to make sure they don't exceed the forecasts! I set the chart up at work today & got good results.
* On the first trick that is 2 charts on top of each other, the top one is transparent.
* On the second trick that is a single stacked bar chart with series grouped.
* There is another trick, you can have one chart with 2 Y-axes (primary and secondary) with the same min and max. On primary, used cluster bar, on secondary use stacked bar (or other way). Make the secondary invisible.
Don't forget to use [CODE]your code here[/CODE] when posting code
If your question was answered please use Thread Tools to mark your thread [RESOLVED]
[RESOLVED] Combination chart problem
Reply With Quote
) 
Originally Posted by Resource Dragon
