Bar chart - variance calculation

[juul2222]

Rookie here, so take it easy on me. I'm trying to make a simple chart that shows the cost to move into two separate locations, and I'd like to graphically show the variance between location A and B but not as a separate bar. Something like a dotted line between the two with the dollar amount shown. Ideas?

[TABLE="width: 402"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Projects[/TD]
[TD]Location A[/TD]
[TD]Location B[/TD]
[/TR]
[TR]
[TD]Base CEC - 400[/TD]
[TD] $ 8,275,685[/TD]
[TD] $ 9,792,741[/TD]
[/TR]
[TR]
[TD]Incremental CEC - 505[/TD]
[TD] $ 20,042[/TD]
[TD] $ 1,521,014[/TD]
[/TR]
[TR]
[TD]Total CEC[/TD]
[TD] $ 8,295,727[/TD]
[TD] $ 11,313,755[/TD]
[/TR]
[TR]
[TD]Incremental EMS/Security[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]NET[/TD]
[TD] $ 8,295,727[/TD]
[TD] $ 11,313,755[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

You might consider a Stacked Column. I tweaked your data to look like this:

	A	B	C	D
Projects	Location A	Location B	Location B
Base CEC - 400
Incremental CEC - 505
Total CEC
Incremental EMS/Security
NET

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]$8,275,685 [/TD]
[TD="align: right"]$1,517,056 [/TD]
[TD="align: right"]$9,792,741 [/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]$20,042 [/TD]
[TD="align: right"]$1,500,972 [/TD]
[TD="align: right"]$1,521,014 [/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]$8,295,727 [/TD]
[TD="align: right"]$3,018,028 [/TD]
[TD="align: right"]$11,313,755 [/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]$0 [/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]$8,295,727 [/TD]
[TD="align: right"]$3,018,028 [/TD]
[TD="align: right"]$11,313,755 [/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=D2-B2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



The location B column is now the difference. Now just select A1:C6, from the Insert tab > Recommended Charts > Stacked Column > OK. If you hover your mouse over the orange or blue parts of the bars, you'll see the differential.

Hope this helps.

 

[juul2222]

Ideally, I would have location A and B side by side, and the actual variance would be displayed. This will be put on an executive summary and the audience won't be able to hover. With that said, A is always lower than B, so if I could stack the variance on top of A, that could work but I can't seem to figure that out.

 

[Eric W]

That's how I originally designed it, with the variance on top of location A. Did you put the variance formula in C, not the actual location B values? And then only select A:C, not A:D?

Another option is to leave the location A and B columns alone, and add another column to the right with Variance as the heading. Put the =C2-B2 formula in column D. Then pick the Clustered Column type of chart.

 

[juul2222]

First of all, thanks so much! And, yes, I did A:D, but A:C only gives you one bar for each scenario as opposed to the locations side by side. When you do the variance in column D and do a clustered column, the variance shows as a third bar, which isn't a good representation. I found the following link to be the exact thing I'm looking for, but I can't recreate it. When I try to copy and paste the example into my worksheet, the table doesn't come with it. I can recreate the table, but I don't know how to create a stacked chart with some columns but not others.

https://www.excelcampus.com/charts/variance-clustered-column-bar-chart/

 

[thisoldman]

For a bar chart, try this.

<tbody>
[TD="class: xl68"][/TD]
[TD="class: xl68, align: center"]A
[/TD]
[TD="class: xl68, width: 89, align: center"]B[/TD]
[TD="class: xl68, width: 89, align: center"]C[/TD]
[TD="class: xl68, width: 89, align: center"]D[/TD]
[TD="class: xl68, width: 89, align: center"]E[/TD]

[TD="class: xl68, align: right"]1[/TD]
[TD="class: xl66"]Projects[/TD]
[TD="class: xl67"] Location B[/TD]
[TD="class: xl67"] Location A[/TD]
[TD="class: xl67"] Base[/TD]
[TD="class: xl67"] Variance
[/TD]

[TD="class: xl68, align: right"]2[/TD]
[TD="class: xl68"]NET[/TD]
[TD="class: xl69"] $ 11,313,755[/TD]
[TD="class: xl69"] $ 8,295,727[/TD]
[TD="class: xl69"] $ 8,295,727[/TD]
[TD="class: xl69"] $ 3,018,028[/TD]

[TD="class: xl68, align: right"]3[/TD]
[TD="class: xl68"]Incremental EMS/Security[/TD]
[TD="class: xl69"] $ -[/TD]
[TD="class: xl69"] $ -[/TD]
[TD="class: xl69"] $ -[/TD]
[TD="class: xl69"] $ -[/TD]

[TD="class: xl68, align: right"]4[/TD]
[TD="class: xl68"]Total CEC[/TD]
[TD="class: xl69"] $ 11,313,755[/TD]
[TD="class: xl69"] $ 8,295,727[/TD]
[TD="class: xl69"] $ 8,295,727[/TD]
[TD="class: xl69"] $ 3,018,028[/TD]

[TD="class: xl68, align: right"]5[/TD]
[TD="class: xl68"]Incremental CEC - 505[/TD]
[TD="class: xl69"] $ 1,521,014[/TD]
[TD="class: xl69"] $ 20,042[/TD]
[TD="class: xl69"] $ 20,042[/TD]
[TD="class: xl69"] $ 1,500,972[/TD]

[TD="class: xl68, align: right"]6[/TD]
[TD="class: xl68"]Base CEC - 400[/TD]
[TD="class: xl69"] $ 9,792,741[/TD]
[TD="class: xl69"] $ 8,275,685[/TD]
[TD="class: xl69"] $ 8,275,685[/TD]
[TD="class: xl69"] $ 1,517,056[/TD]

</tbody>

Note: Location A and Location B columns have been interchanged. The order of projects has been reversed. Bar charts require some non-intuitive data-source configurations.

D2:: =C2
E2:: =MAX(B2:C2)-MIN(B2:C2)

Select A1:E6. Insert a Stacked Bar chart.

Select the vertical axis. Bring up the Format pane, Ctrl+1. Select the Bar chart icon, "Axis Options".

Click on Location B series in the chart. In the format pane, set it on the secondary axis. Repeat for the Location A series.

Tip: You can select chart items directly in the format pane. The second line in the pane, just above the icons, has a small downward pointing arrow. Click on the little arrow and make your item selection in the pop-up list box.

From the Design tab, or from the chart's right-click context menu, click on "Change Chart Type". Change one of Location A or Location B series in the pop-up to "Clustered Bar". The other Location series should change simultaneously.

From there, you should be able to follow the ExcelCampus tutorial.

I found Gap widths of 60% and 30% seemed to look OK. To eliminate "Base" from the Legend, select the legend, then select "Base" and press Delete. I formatted the upper axis to the Custom Number Format:
$ 0,,"M";;$ 0
As a final step, I deleted the lower axis.

You're on your own for the data labels.