[RESOLVED] Combination chart problem

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.

Reso

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?

Spoo

can you do this manually in exel?
if so try recording a macro to generate some sample code

Thanks, Spoo, Westconn.

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!)

cheers

The Dragon

Dragon

If the macro recording route doesn't prove satisfactory, then
the "brute force" method I outlined earlier may be an alternative.

Spoo

Pure Excel allows stacked or cluster bars mixed with line or point but does not allow to mix stacked and cluster bars.

However, you can "visually" mix stacked and cluster bars as attached image with some tricks/cheating (without VBA).

I don't understand the term "brute force" in this case.

anhn

OK, I give ... how did you do that ??
(btw, way cool :thumb:)

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.

Spoo

Dear anhn

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.:p)
Noted that there are a blank row and many blank columns on the data table.
.

Dear anhn

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.

cheers

The Dragon

Dear anhn

Yes, that is what you did. It's a brillliant solution. Thank you very much.

One happy Resource Dragon

Quote:

---

Originally Posted by Resource Dragon

It's a brillliant solution.

---

anhn

Ditto

Spoo

Quote:

---

You've actually created two stacked charts

---

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.

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.

Cheers

The Dragon

Good to hear it's work for you.

* 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.