Percentages Wrong in 100% Stacked Column

[bwhi]

Hello!

I have a spreadsheet that documents the amount of trash a company is disposing of each month vs. the projected amount. I am using a 100% stacked column chart. The issue is that the "actual" amount in the chart is not correct. I did some research that suggested I change the Y axis to start at 0 but it seems that it already does, so I'm not sure what I'm doing wrong. I'm a beginner at this so I'm a little confused. Any guidance would be appreciated! I've attached the file for reference.

Thanks!

[MrShorty]

It looks to me like the stacked column is doing exactly what it should. For January, for example, it is stacking 1500 on top of 1268 (for a total of 2768), then dividing each "stack" by this total. 1268/2768 is about 45%.

It looks like what you intend is to get the ratio of the first column over the second column rather than the sum of the 2 columns. To get this, you will need a difference column. Something like =C2-B2 (or whatever the equivalent structured table reference is), then use this difference column as the second data series in the stacked column chart.

[bwhi]

Thank you so much, you saved the day! I made the changes and got exactly what I needed. I definitely wouldn't have been able to figure that out on my own. Thanks again!

[bwhi]

Hi again! So that did solve my problem, but now as I'm going through and changing the chart for each company I've run into another issue. One company made 119% of their projected tonnage, with a difference of -19.92. Now that the difference is negative, the chart looks wrong. Would you mind looking at it and letting me know if that's how it should look? Thank you!

[MrShorty]

Now the chart is a regular stacked column rather than a 100% stacked column, but the chart looks correct for the values that are fed into it. I am not sure exactly what your are trying to show with this chart, but I don't think the stacked column is going to work like this.

My first thought would be to use a clustered column chart, where you show the actual and projected as adjacent columns (take your chart from the OP and change the chart type to clustered column).

My second thought, especially because the projected value is constant across the year, would be a combination column + line chart. Take the clustered column chart from the previous thought and change the chart type of the "projected" series to line.

See if those help communicate what you are trying to show here.