Gap to Target Column

[dsciola]

I want to create a chart that shows "Gap to Target. Please see attached Sample spreadsheet for more info.

Note how the yellow part of the chart is formulaic...is actually the chart...and the green bar over it is manually pasted. That green bar is supposed to be "result" and that yellow gap is supposed to represent the difference between Targeted sales and the actual Result.

Any guidance on how to actually build the green part into the chart? Every time I try, it just adds them together and doesnt show the gap. Any thoughts appreciated.

Thanks,
Dom

[Glenn Kennedy]

Hi. I think/hope that this is what you want.

[Andy Pope]

If you use 2 series with actual on secondary axis you can create a bullet chart and display both positive and negative gaps.

[dsciola]

Sweet...all pretty helpful.

So it's just a clustered bar chart with the Result series on the secondary axis then? Guess I was way off thinking a stacked/100% stacked chart was the answer...did u just turn off the axes too? Every time I tried that, the chart went really screwy...like the Result or Target series would just cover the whole chart.

thanks!
Dom

[Glenn Kennedy]

I did a 100% stacked bar, one element being Q3 income and the other, the gap. I deleted the axes and then had lots of fun resizing it so that it fitted into the space you'd allowed.

[dsciola]

If you use 2 series with actual on secondary axis you can create a bullet chart and display both positive and negative gaps.

Hey Andy thanks again for helping me out here. Quick question, how were you able to delete the axis labels and numerical values without getting the chart to re-size? Every time I try to get rid of the top and bottom axes, the chart re-sizes and it looks off. Please let me know if you can help.

Thx again,

Dom

[Andy Pope]

They are not deleted. I just set ticklabels, tickmarks and Line to none.

[dsciola]

Nice! Think it now works. Thanks so much!