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]

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

 

[awoohaw]

Here is updated chart formulas with chart building instructions:

 

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