Making min-max-average bar charts work

[jaakovs]

Hello everybody,


I'm trying to recreate this graphical representation for my data, which is an aggregation that consists of many series each with three columns: min, max, and average in the range from 1.0 to 4.0. I've experimented with floating bar charts, where I've created a fourth column with the difference between the min and the max, and then plotted that column stacked with the min values, with the bar for the min values made invisible.


Do any of you have ideas on how to recreate this visualization? Is there a way for me to plot the averages with markers similar to those pictured? What's the best way for me to overlay this color-coded range?

Thanks so much!

 

[Worf]

Hello

o Here is a link to my test workbook: Dropbox - line-chart-with-bands.xlsm
o I used some VBA to format text boxes and data labels.
o Tell me if you need further explanations

Sub DL()
Dim dtlb As DataLabel, sh As Shape, a, i%
a = Array("tb6", "tb7", "tb9", "tb8")
For i = LBound(a) To UBound(a)
    Set sh = ActiveSheet.Shapes(a(i))
    sh.Left = ActiveChart.Parent.Left + ActiveChart.PlotArea.InsideLeft + i * 82.5 + 4
    sh.Top = 384
    sh.Width = 82.5
    sh.Height = 19
Next
For Each dtlb In ActiveChart.SeriesCollection(5).DataLabels
    dtlb.Height = 17
    dtlb.Width = 7
Next
End Sub

 

[jaakovs]

Thanks so much for working on a solution for my problem. I examined the spreadsheet that you sent me.


Is it possible to have the gray bars match the range between the minimum and maximum values for each series? I've got my source data organized as follows, where the numerical columns are max, min, and average values for each series, respectively.

Also, I'm new to VBA, so I'm doing my best to understand how I can take your code and apply it to my own data. Can you explain what parts of the code I need to modify so that it reads off my data and produces that visualization in my own worksheet?


Thanks! I appreciate the input.

 

[Worf]

Yes, it is possible, see example below.
I will prepare a worksheet layout that respects your source data structure and generates the chart.

 

[jaakovs]

Thank you so much, Worf, I look forward to taking a look at your worksheet.

 

[Worf]

See below how it looks like, the formulas used and a link to the workbook. Note that no VBA is required.


Plan2 (2)

	C	D	E	F	G	H	I	J	K	L

<tbody>
[TD="align: center"]34[/TD]
[TD="align: center"]Business[/TD]
[TD="align: center"]1,75[/TD]
[TD="align: center"]0,75[/TD]
[TD="align: center"]0,75[/TD]
[TD="align: center"]0,75[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0,8[/TD]
[TD="align: center"]0,06[/TD]
[TD="align: center"]1,17[/TD]

[TD="align: center"]35[/TD]
[TD="align: center"]Mining[/TD]
[TD="align: center"]1,75[/TD]
[TD="align: center"]0,75[/TD]
[TD="align: center"]0,75[/TD]
[TD="align: center"]0,75[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0,17[/TD]
[TD="align: center"]0,06[/TD]
[TD="align: center"]0,8[/TD]

[TD="align: center"]36[/TD]
[TD="align: center"]Stability[/TD]
[TD="align: center"]1,75[/TD]
[TD="align: center"]0,75[/TD]
[TD="align: center"]0,75[/TD]
[TD="align: center"]0,75[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1,47[/TD]
[TD="align: center"]0,06[/TD]
[TD="align: center"]0,5[/TD]

[TD="align: center"]37[/TD]
[TD="align: center"]Skills[/TD]
[TD="align: center"]1,75[/TD]
[TD="align: center"]0,75[/TD]
[TD="align: center"]0,75[/TD]
[TD="align: center"]0,75[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1,22[/TD]
[TD="align: center"]0,06[/TD]
[TD="align: center"]0,75[/TD]

[TD="align: center"]38[/TD]
[TD="align: center"]Health[/TD]
[TD="align: center"]1,75[/TD]
[TD="align: center"]0,75[/TD]
[TD="align: center"]0,75[/TD]
[TD="align: center"]0,75[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0,72[/TD]
[TD="align: center"]0,06[/TD]
[TD="align: center"]0,25[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula I34 =C3 J34 =D3-C3-0.03 L34 =B3-D3 I35 =C4 J35 =D4-C4-0.03 L35 =B4-D4 I36 =C5 J36 =D5-C5-0.03 L36 =B5-D5 I37 =C6 J37 =D6-C6-0.03 L37 =B6-D6 I38 =C7 J38 =D7-C7-0.03 L38 =B7-D7 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Dropbox - chart-with-bands.xlsm

 

[jaakovs]

Hello Worf - your solution at has worked really well! I recommend this to anyone who's looking at creating something similar. Thank you so much for all the time and effort you put into it.

Quick question - the chart reads my source data in reverse order for some reason. In other words, the first series (and corresponding bar) are the last ones in my dataset. Do you know why this is happening and how I might be able to fix it?

 

[thisoldman]

Quick question - the chart reads my source data in reverse order for some reason. In other words, the first series (and corresponding bar) are the last ones in my dataset. Do you know why this is happening and how I might be able to fix it?

The default for bar charts is to have the first category at the bottom-most position. Select the vertical axis, the category axis, and in the formatting options under Size, will be a checkbox labeled "Categories in reverse order". Check that box and the chart will display as you want.

 

[jaakovs]

The default for bar charts is to have the first category at the bottom-most position. Select the vertical axis, the category axis, and in the formatting options under Size, will be a checkbox labeled "Categories in reverse order". Check that box and the chart will display as you want.

I selected the vertical axis and under axis options, I checked the "categories in reverse order" box. It reversed the categories, but not the bars associated with them - they were unchanged. So when I check that option, the bars no longer reflect the correct data for each category.

 

[thisoldman]

I should have known this just by looking at the chart: there are two vertical axes. Reversing the categories for the primary vertical axis is only half the job.

The other vertical axis:
When you select the chart or any item on the chart, three helper icons appear to the right of the chart. Click on the "plus" icon, click on the small rightward pointing triangle and check the box for the secondary vertical axis to display.

Reverse the categories on this axis as well as the primary axis. Then bring up the Chart Elements menu again and uncheck the secondary vertical axis checkbox. I believe that should fix everything.