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]

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)

 

[awoohaw]

Note: the formula grid above is not super dynamic, but you can create formulas that do what you ask, with the LET/HSTACK/VSTACK/FILTER/UNIQUE functions that are in Office 365. (if you have that).

 

[SerenityNetworks]

Whoohoo! Thank you so very much. Step 5 was what I was missing. I didn't notice that the second button in Combo Charts had the "with Line on Secondary Axis". I was always using the first button and thinking I could choose the secondary axis for the needed fields later when changing the chart type. That was wrong, wrong, wrong. Once I selected the "Clustered Column Chart with Line on Secondary Axis" the rest was as I remembered. I can do it with dynamic formulas or a pivot table. To clean up the pivot table to not show lines for no and n/a percentages, plus tweaking the legend labels to look decent, takes just as much effort (if not more) as adding a new element to a summary table. So I'll probably go with your method, rather than a pivot table.

Thanks again for getting me past that forgotten little detail that was killing me! Much appreciated.
Andrew

 

[awoohaw]

Happy to help. I you think a post has provided you with an answer to your question, please mark it as the solution.