Variable width histogram - assign values to ranges

posted Aug 5, 2013, 8:42 AM by Krisztina Szabó   [ updated Aug 5, 2013, 9:35 AM by r ]
[as monster runs out the door]
The Blindman: Wait. Where are you going? I was going to make Espresso.

by The FrankensTeam

Sergio asked us to create a special histogram where the categories are not the same size. He has age intervals and he would like to assign a value to those - in other words, the widths of the rectangles on the histogram should represent widths of the age intervals.

We made a chart-based solution and a conditional formatting based one adapted for his data tables. In both cases we use named formulas and dynamic ranges for all the background calculations, so the models are dynamic, you can add new data to the tables.

Data structure

The original data structure looked something like:

As you can see  the start and end of the intervals appear in two columns.
Please remember, for a correct visual representation, it is important to have connecting intervals.

Error-bar histogram

There is only one data point series where the starting points of the intervals are combined with the end point of the very-last interval. The technique of error bars was used to draw the borders of the squares and an additional data series were added to position the data labels above the columns.
You can download the file for this data structure.


And here is a special (also dynamic) version: the chart is drawn with the help of conditional formatting and mouse-rollover to highlight the column and the legend. You can simply move the mouse over a column - the legend of that column will be highlighted. Or you can move the mouse over the legend - the column will be highlighted.

You can download the file for this version too.

Aug 5, 2013, 8:42 AM
Aug 5, 2013, 8:42 AM