I would like to know how to put in percentage trend lines for individual levels in a stacked bar chart using the data below. I want a usage trend line for the total, as well as certain specific levels.
I appreciate any tips you can give me and am sorry I cant describe it better in words, but believe the images will clarify.
Data Example
(click above for larger image)
It's a little irregular to compute trendlines for stacked series; Excel doesn't even want to do it. But we're smarter than Excel, so here's how to do it.
I'll do it for the top data set; the protocol is the same for the other set also.
First, I added some cumulative calculations. Cum1 is just series 1. Cum2 is the sum of series 1 and 2. Cum3 is the sum of series 1, 2, and 3. So I'm calculating the height of the first stacked bar (Cum1), the second stacked bar (Cum2), etc.
I built a stacked column chart from the first six series in the first chart.
In chart 2 I've added the Cum1 through Cum6 data, which Excel adds as more stacked columns, and I converted them to simple line and marker series.
In chart 3 I deleted the legend, because it's just going to get larger and more confusing. I also selected the lowest line chart series (Cum1), clicked the plus icon next to the chart, and checked Trendline. You can see the dotted trendline though it's mostly obscured by the data series itself (the lines and markers).
In chart 4 I hid the Cum1 series, by formatting it to have no line and no marker.
In chart 5 I added trendlines for the other series (Cum2 through Cum6).
In chart 6 I hid the other series (Cum2 through Cum6).
I would probably format the trendlines with solid, not dotted, lines. I would also try to do something with the colors, because series 5 and 6 are not distinguishable. But you get the idea.
