Combination Chart, Stacked Columns with a Line for Percent

[SerenityNetworks]

I have data similar to what is shown in the table below. I'd like to produce a combo chart with stacked columns and a line. Along the x-axis should be the class numbers. The stacked columns should show graduated (Yes, No, and N/A). The line should should the percentage of graduated equaling yes. I do not want lines for graduated equaling no or n/a. It has been a few years since I've done this sort of graph and for the life of me I can't recall how to do it. Any help will be appreciated.

Class Number	Name	Other Data	Graduated
1	Bob	Red	Yes
1	Ted	Orange	Yes
1	Carol	Yellow	Yes
1	Alice	Green	No
2	Andrew	Blue	Yes
2	Lamar	Indigo	No
3	Jean	Violet	Yes
3	Pat	Thin	Yes
3	Tim	Thick	Yes
3	Craig	N/A	No
3	Ann	Solid	N/A
3	Sheila	Liquid	N/A
3	Jeff	Gag	No
...through class 45

 

[awoohaw]

Can you help define what you want in the columns? I see 2 or 3 different kinds of values. But, they are still unique by name.
Where is the summary data that the chart is actually based on?

 

[awoohaw]

i re-read your question. I guess the 2 center columns are not important in this scenario.

 

[awoohaw]

Somthing Like this:

mr excel questions 21.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Class Number	Name	Other Data	Graduated			Yes	No	N/A	%Yes
2	1	Bob	Red	Yes		1	3	1	0	43%
3	1	Ted	Orange	Yes		2	1	1	0	14%
4	1	Carol	Yellow	Yes		3	3	2	2	43%
5	1	Alice	Green	No
6	2	Andrew	Blue	Yes
7	2	Lamar	Indigo	No
8	3	Jean	Violet	Yes
9	3	Pat	Thin	Yes
10	3	Tim	Thick	Yes
11	3	Craig	N/A	No
12	3	Ann	Solid	N/A
13	3	Sheila	Liquid	N/A
14	3	Jeff	Gag	No
Sheet8

Cell Formulas
Range	Formula
G2:I4	G2	=COUNTIFS($A$2:$A$14,$F2,$D$2:$D$14,G$1)
J2:J4	J2	=G2/SUM($G$2:$G$4)

 

[SerenityNetworks]

Thanks for the quick response. I'm not sure what information you are asking for beyond what I have already provided. I have attached a mock-up of what I am looking to produce. Does this help?

PS. I'll be offline until this evening, so please do not expect a response again before then.

Thanks again,
Andrew

 

[awoohaw]

DId you see Post #4?

 

[SerenityNetworks]

I just saw your reply, but I must rush off. You have the idea. Your percentages are not what I expect, but your graph is what I envision. I need to know how to produce the graph.

Thanks again,
Andrew

 

[awoohaw]

Well, I thought calculated "percentage of graduated equaling yes" for the records in your sample.

When you get back, to avoid any more confusion maybe can post the source data elements as you have them calculated.

 

[SerenityNetworks]

I see. You were giving the percentage of yeses per class on the total number of yeses. I needed the percentage of yeses per class on the total number in the class.

But either way, I still do not see how to make the combo graph you generated. I was hoping to create it from a pivot table and graph. In that way it can be more dynamic. For example right now I have "Yes", "No", and "N/A", but later I might add something more specific than "N/A" like "Deceased" or "Moved". A pivot table would pick that up automatically from the data. If I'm generating the graph from a secondary/summary table then I would need to rebuild the secondary/summary table. But I'm open to solutions. Right now, I can't create the type of graph I need. That is where I am stuck.

Thanks again,
Andrew

 

[awoohaw]

1. I made a short screen recording of how to make the chart. Send me your email in a DM and I'll send it to you. Or give me a drop box location to upload it to.
2. I hate pivots, I prefer my reporting via automatically updating and dynamic formula grids. But regardless, the data needs to be calculated somehow. But, because of that, I have no idea how to calculate percentage of all in a pivot.
3. Here is the updated worksheet with the percentage of all in the formulas.

mr excel questions 21.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Class Number	Name	Other Data	Graduated			Yes	No	N/A	%Yes
2	1	Bob	Red	Yes		1	3	1	0	23%
3	1	Ted	Orange	Yes		2	1	1	0	8%
4	1	Carol	Yellow	Yes		3	3	2	2	23%
5	1	Alice	Green	No
6	2	Andrew	Blue	Yes
7	2	Lamar	Indigo	No
8	3	Jean	Violet	Yes
9	3	Pat	Thin	Yes
10	3	Tim	Thick	Yes
11	3	Craig	N/A	No
12	3	Ann	Solid	N/A
13	3	Sheila	Liquid	N/A
14	3	Jeff	Gag	No
15
16
17
SerenityNetworks

Cell Formulas
Range	Formula
G2:I4	G2	=COUNTIFS($A$2:$A$14,$F2,$D$2:$D$14,G$1)
J2:J4	J2	=G2/SUM($G$2:I$4)