Making min-max-average bar charts work

jaakovs

New Member
Joined
May 22, 2017
Messages
12
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!
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,010
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


Code:
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

New Member
Joined
May 22, 2017
Messages
12
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

Well-known Member
Joined
Oct 30, 2011
Messages
4,010
Yes, it is possible, see example below.
I will prepare a worksheet layout that respects your source data structure and generates the chart.

 

jaakovs

New Member
Joined
May 22, 2017
Messages
12

ADVERTISEMENT

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

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,010
See below how it looks like, the formulas used and a link to the workbook. Note that no VBA is required.


Plan2 (2)

CDEFGHIJKL
34Business1,750,750,750,75020,80,061,17
35Mining1,750,750,750,75010,170,060,8
36Stability1,750,750,750,75011,470,060,5
37Skills1,750,750,750,75021,220,060,75
38Health1,750,750,750,75010,720,060,25

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
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
 
Last edited:

jaakovs

New Member
Joined
May 22, 2017
Messages
12

ADVERTISEMENT

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

Well-known Member
Joined
Jan 5, 2014
Messages
1,074
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

New Member
Joined
May 22, 2017
Messages
12
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

Well-known Member
Joined
Jan 5, 2014
Messages
1,074
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,937
Messages
5,525,731
Members
409,661
Latest member
pprabha

This Week's Hot Topics

Top