Help creating a diverging stacked bar chart?

T

T1000

New Member
Joined
Jul 2, 2014
Messages
6
Hi does anyone know how to create the above? I have some data from questions that give 4 possible results to which I've attrubuted some scoring but the negative response have a negative score.

If anyone can help out I'll post up a small example data set (5 rows, 4 columns)
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
I want to create something like this.........

DivergingStackedBarChart.png
 
Upvote 0
[TABLE="width: 1039"]
<colgroup><col span="4"><col><col span="11"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]max poss score[/TD]
[TD="align: right"]100%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BIN[/TD]
[TD]BIN RATING[/TD]
[TD]FREQ[/TD]
[TD][/TD]
[TD][/TD]
[TD]to plot[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1-5[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]1.1[/TD]
[TD="align: right"]1.1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1.1[/TD]
[TD]0.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6-7[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]45.5[/TD]
[TD="align: right"]6.8[/TD]
[TD="align: right"]6.8[/TD]
[TD][/TD]
[TD][/TD]
[TD]6.8[/TD]
[TD]8.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]centre A[/TD]
[TD][/TD]
[TD]8-9[/TD]
[TD="align: right"]8.5[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]374[/TD]
[TD="align: right"]55.8[/TD]
[TD="align: right"]55.8[/TD]
[TD][/TD]
[TD][/TD]
[TD]55.8[/TD]
[TD]58.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]19.4[/TD]
[TD="align: right"]19.4[/TD]
[TD][/TD]
[TD][/TD]
[TD]19.4[/TD]
[TD]20.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]557[/TD]
[TD="align: right"]83.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]670[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]above table is plotted as horizontal bar chart[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BIN[/TD]
[TD]BIN RATING[/TD]
[TD]FREQ[/TD]
[TD][/TD]
[TD][/TD]
[TD]to plot[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1-5[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0.7[/TD]
[TD="align: right"]0.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6-7[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]58.5[/TD]
[TD="align: right"]8.7[/TD]
[TD="align: right"]8.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]centre B[/TD]
[TD][/TD]
[TD]8-9[/TD]
[TD="align: right"]8.5[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]391[/TD]
[TD="align: right"]58.4[/TD]
[TD="align: right"]58.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]20.9[/TD]
[TD="align: right"]20.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]594.5[/TD]
[TD="align: right"]88.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]710[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Sorry I found the above a little confusing to follow, here is one of my examples.......

[TABLE="width: 472"]
<TBODY>[TR]
[TD][/TD]
[TD]Good</SPAN>[/TD]
[TD]Adequate</SPAN>[/TD]
[TD]Just Adequate</SPAN>[/TD]
[TD]Inadequate</SPAN>[/TD]
[/TR]
[TR]
[TD]Q30</SPAN>[/TD]
[TD="align: right"]676</SPAN>[/TD]
[TD="align: right"]190</SPAN>[/TD]
[TD="align: right"]-47</SPAN>[/TD]
[TD="align: right"]-62</SPAN>[/TD]
[/TR]
[TR]
[TD]Q31</SPAN>[/TD]
[TD="align: right"]512</SPAN>[/TD]
[TD="align: right"]285</SPAN>[/TD]
[TD="align: right"]-113</SPAN>[/TD]
[TD="align: right"]-156</SPAN>[/TD]
[/TR]
[TR]
[TD]Q33</SPAN>[/TD]
[TD="align: right"]690</SPAN>[/TD]
[TD="align: right"]158</SPAN>[/TD]
[TD="align: right"]-30</SPAN>[/TD]
[TD="align: right"]-16</SPAN>[/TD]
[/TR]
[TR]
[TD]Q37</SPAN>[/TD]
[TD="align: right"]866</SPAN>[/TD]
[TD="align: right"]150</SPAN>[/TD]
[TD="align: right"]-35</SPAN>[/TD]
[TD="align: right"]-56</SPAN>[/TD]
[/TR]
[TR]
[TD]Q39</SPAN>[/TD]
[TD="align: right"]662</SPAN>[/TD]
[TD="align: right"]163</SPAN>[/TD]
[TD="align: right"]-47</SPAN>[/TD]
[TD="align: right"]-50</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]


The categories in bold will be coloured coded on the chart from green to red.
 
Upvote 0
not all questions will have the same number of answers especially between different call centres, I gave a score to each category and calculated 1.1 6.8 55.8 19.4 for call centre A (max is 100) you only need to plot that group of 4 numbers for each call centre

do you want a bar chart for each question ?
 
Upvote 0
oldbrewer said:
not all questions will have the same number of answers especially between different call centres, I gave a score to each category and calculated 1.1 6.8 55.8 19.4 for call centre A (max is 100) you only need to plot that group of 4 numbers for each call centre

do you want a bar chart for each question ?
Click to expand...

Hi no I'd like to display all 5 questions above on a single horizontal bar chart please. I can then adapt it to for other applications in my datasets.
 
Upvote 0
[TABLE="width: 832"]
<colgroup><col span="13"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Good[/TD]
[TD]Adequate[/TD]
[TD]Just Adequate[/TD]
[TD="colspan: 2"]Inadequate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Q30[/TD]
[TD="align: right"]676[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]62[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Q31[/TD]
[TD="align: right"]512[/TD]
[TD="align: right"]285[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]156[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Q33[/TD]
[TD="align: right"]690[/TD]
[TD="align: right"]158[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Q37[/TD]
[TD="align: right"]866[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]56[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Q39[/TD]
[TD="align: right"]662[/TD]
[TD="align: right"]163[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 11"]
clip_image001.gif

<tbody>
</tbody>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Gloss Colours in A Stacked Bar Chart
Replies
2
Views
115
SteveP29
S
W
  • Question Question
Charting range as a vertical bar chart
Replies
1
Views
216
felixstraube
F
A
  • Question Question
Creating a Pie Chart of a segment of a stacked bar in a Grouped Bar Chart (the bar chart I made is attached)
Replies
0
Views
323
afrailer
A
K
  • Question Question
This is driving me NUTS!! I just want to plot a stacked bar next to a regular bar
Replies
1
Views
196
RoryA
RoryA
J
  • Solved
Calculate sums based on values
Replies
3
Views
297
NateSC
NateSC

Forum statistics

Threads
1,224,531
Messages
6,179,380
Members
452,907
Latest member
Roland Deschain

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top