How can I combine a stacked bar chart with stacked colum chart?

[Sin]

I have created a Stacked column graph showing 5 layers of amount, within each
layer, the amounts is shared between 3 parties in different proportions, I
was the graph to show the different proportion between the parties in each
layer - i.e. like creating bar chart in each layer or a pie chart effect in
each layer ( or stacked column), is this possible to achieve?

 

[Jon Peltier]

Perhaps this is what you want:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=508

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Sin]

Thx Jon.

Do you have any instruction on how to construct the Matrix Bar Chart? In
addition, the article mentioned manual labeling method, does this mean using
the Drawing tools or what is the method is it referring to? Does that mean
we won't need to create scatter series if manual labeling was applied?

 

[Jon Peltier]

The article's all about how to produce the chart. Any labels are placed
using dummy XY series to position points where desired, using no lines and
no markers to format the dummy series, and applying data labels to these
points. You can use manual labeling via text boxes, if you don't mind also
manually positioning the labels after any change to the data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Sin]

The article only give instruction on how to produce the Matrix Colum chart,
how can I enhance it to produce the Matric Bar Chart?

 

[Jon Peltier]

Oh, I understand. So many people say 'bar' when they mean 'column', that I
completely missed what you were saying.

The matrix bar chart requires a much more intricate process, because the
stacked area technique cannot be used. What you have to do is make a stacked
bar chart, with a couple hundred thin bars in each series. The bars have an
area fill but no border, and if you want borders, you need to construct XY
series (lines but no markers) to trace them. The basic technique is shown in
Stephen Bullen's variable width column chart example, but you use bars
instead of columns. His is the first link in this page:

http://peltiertech.com/Excel/ChartsHowTo/VariableWidthColumns.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Sin]

I looked at the Stepen's Funchart5 - the chart appears to be constructed by
Visual Basic or other programming language, how can I open the codes to put
in additional series?

From Stepen's example, if I have 3 cost of production (cost 1, cost 2, cost
3) and want them to be included in the stacked graphs, Is the following
amendment correct?

OFFSET WIDTH CHANGED TO 3:
rngVol=OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B)-1,3)

NEW LINE: rngCost2=OFFSET(rngVol,0,3)

NEW LINE: rngCost3=OFFSET(rngVol,0,4)

AMENDMENT TO CREATE ADDITION BLOCKS FOR EARCH SERIES:
rngSer1=(IF(rngPlant=1,INDEX(rngCost1,1),0)),
(IF(rngPlant=1,INDEX(rngCost2,1),0)), (IF(rngPlant=1,INDEX(rngCost3,1),0)) -
repeat for each series

 

[Jon Peltier]

Stephen's example uses no VBA. It's a little obscure unless you have a lot
of experience with named ranges and the like. Perhaps I can give an easier
example. I'll stick with Stephen's data:

PLANT VOLUME CUM VOLUME COST
A 10 0 50
B 60 10 30
C 40 70 65
D 130 110 45

The total volume is 240, so the simplest thing to do is set up another range
in the worksheet, say columns L. Keep L1 blank. In M11 put the labels A,
B, C and D. In L2:L241 put the numbers 1 to 240 (one row per unit of
volume). The range M2:M11 consists of 10 rows for the 10 units of A volume,
and they contain the value 50, A's cost. The range N12:N71 (60 rows)
contains the value 30. O72:O111 (40 rows) contains the value 65, and
P112241 (110 rows) contains the value 45. In abbreviated form, the range
looks like this:

Col L Col M Col N Col O Col P
Row 1 A B C D
Row 2 1 50
Row 3 2 50
....
Row 10 9 50
Row 11 10 50
Row 12 11 30
Row 13 12 30
....
Row 70 69 30
Row 71 70 30
Row 72 71 65
Row 73 72 65
....
Row 110 109 65
Row 111 110 65
Row 112 111 45
Row 113 112 45
....
Row 240 239 45
Row 241 240 45

The range L1241 is plotted in a stacked column chart in Stephen's example,
or in a stacked bar chart in yours. The columns/bars are formatted with a
fill color but no border.

To make the chart more dynamic, insert five rows above L11. Transpose
Stephen's data and put it into the inserted rows (L14):

Col L Col M Col N Col O Col P
Row 1 A B C D
Row 2 Volume 10 60 40 130
Row 3 Cum Vol 0 10 70 110
Row 4 Cost 50 30 65 45
Row 5
Row 6 A B C D
Row 7 1 50 0 0 0
Row 8 2 50 0 0 0
....
Row 15 9 50 0 0 0
Row 16 10 50 0 0 0
Row 17 11 0 30 0 0
Row 18 12 0 30 0 0
....
Row 75 69 0 30 0 0
Row 76 70 0 30 0 0
Row 77 71 0 0 65 0
Row 78 72 0 0 65 0
....
Row 115 109 0 0 65 0
Row 116 110 0 0 65 0
Row 117 111 0 0 0 45
Row 118 112 0 0 0 45
....
Row 245 239 0 0 0 45
Row 246 240 0 0 0 45

Cell M7 has this formula:

=IF(AND($L7>M$3,$L7<=M$2+M$3),M$4,0)

Copy cell M7, then select the entire range M7246, and Paste. This puts the
formula into the entire range. The chart is now made from the range L6246.

One more refinement will disconnect the length of the chart source data
range from the volume values. Suppose we decide 100 columns/bars in the
chart provides all the resolution we need. Put the numbers 1-100 into
L7:L106, and delete everything from L106107 and below. Change the formula
in M7 to this:

=IF(AND($L7>100*M$3/SUM($M$2:$P$2),$L7<=100*(M$2+M$3)/SUM($M$2:$P$2)),M$4,0)

and fill M7106 with this new formula. The resulting table is much smaller
but the chart is essentially the same:

Col L Col M Col N Col O Col P
Row 1 A B C D
Row 2 Volume 10 60 40 130
Row 3 Cum Vol 0 10 70 110
Row 4 Cost 50 30 65 45
Row 5
Row 6 A B C D
Row 7 1 50 0 0 0
Row 8 2 50 0 0 0
Row 9 3 50 0 0 0
Row 10 4 50 0 0 0
Row 11 5 0 30 0 0
Row 12 6 0 30 0 0
....
Row 34 28 0 30 0 0
Row 35 29 0 30 0 0
Row 36 30 0 0 65 0
Row 37 31 0 0 65 0
....
Row 50 44 0 0 65 0
Row 51 45 0 0 65 0
Row 52 46 0 0 0 45
Row 53 47 0 0 0 45
....
Row 105 99 0 0 0 45
Row 106 100 0 0 0 45

Stephen's technique merely defines some named formulas that represent the
data in the formulas without using a range as above to hold the data.
However, this makes it more difficult to examine and debug the data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Sin]

Thx so much Jon, these are fantastic stuff, they're very helpful. Thx again.