Create histogram for continuous data

[normajean]

Hi all,

Anyone know how to create a histogram for continuous data?

I want an x axis that begins at 0, then half a unit to the right i need it to start at 150 and finish at 200 at 10 unit intervals (classes). Between each of these 10 unit intervals must be a column (no gaps between columns). After the 200 mark I need another half unit just to finish the axis. Axis tick marks need to be on the outside.

x y
150-160 10
160-170 25
170-180 35
180-190 28
190-200 12

(but the x axis labels cant be "150 - 160"; i need "150" at the first tick mark, then "160" at the second tick mark, and so on. I've just provided the range above because they are columns representing a range)

I can create a column graph and delete the axis labels and put them in as text boxes to get the labeling right. But I can't get a half space at the beginning or at the end.

I have also managed to get pretty close by using error bars. Unfortunately I can't get them to show as tick marks on the outside because to allow them to show, I need to extend the vertical axis below zero.

 

[normajean]

Guys I think I just about have it now.

I created a fake x axis and a fake y axis using x y scatterplots and error bars. The 'real' x and y axes had to be extended down and tothe left, respectively, to allow both sets of error bars to show. Then Iselected not the show the 'real' x and y axes. Then I just need to put in the axis labels as text boxes and the axes are done.

I also needed horizontal major grid lines. So I created those with error bars as well. HOWEVER, these error bars are in front of my columns. I'm now trying to figure out how to get the error bars for the gridlines BEHIND the columns. Any ideas?

 

[thisoldman]

It seems Excel always puts lines over columns in combination charts. See if this alternative method works for you.

This is the data to chart. The first column is text, not numbers. The top left cell is blank. If you add a label, Excel will coerce the labels in the first column into a data series.

[TABLE="class: grid"]
<tbody>[TR]
[TD="class: xl67"][/TD]
[TD="class: xl68"]Data[/TD]
[TD="class: xl68"]Dummy[/TD]
[/TR]
[TR]
[TD="class: xl69"]0[/TD]
[TD="class: xl70, align: right"]0[/TD]
[TD="class: xl70, align: right"]35[/TD]
[/TR]
[TR]
[TD="class: xl65"]150[/TD]
[TD="class: xl66, align: right"]10[/TD]
[TD="class: xl66, align: right"]35[/TD]
[/TR]
[TR]
[TD="class: xl65"]160[/TD]
[TD="class: xl66, align: right"]25[/TD]
[TD="class: xl66, align: right"]35[/TD]
[/TR]
[TR]
[TD="class: xl65"]170[/TD]
[TD="class: xl66, align: right"]35[/TD]
[TD="class: xl66, align: right"]35[/TD]
[/TR]
[TR]
[TD="class: xl65"]180[/TD]
[TD="class: xl66, align: right"]28[/TD]
[TD="class: xl66, align: right"]35[/TD]
[/TR]
[TR]
[TD="class: xl65"]190[/TD]
[TD="class: xl66, align: right"]12[/TD]
[TD="class: xl66, align: right"]35[/TD]
[/TR]
[TR]
[TD="class: xl65"]200[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]35[/TD]
[/TR]
</tbody>[/TABLE]

Fig 1 — Plot the data as a column chart. I deleted the title and the legend. The horizontal axis is set to 2 pts, dark red, with outside ticks to emphasize it here.

Fig 2 — The Dummy series is put on the secondary vertical axis. Add the secondary horizontal axis. You can add this axis from the chart's plus-icon menu.

Fig 3 — Delete the secondary vertical axis. The secondary horizontal axis is set to no line and no tick marks (you can do this later, if you wish). The Dummy series is set to no fill and no line.

Fig 4 — The secondary horizontal axis position is set to "On tick marks".

Fig 5 — With the secondary horizontal axis selected, from the formatting panel, under the column-chart icon, in the section "Labels", set the "Label position" to "Low". Repeat for the primary horizontal axis, but set the "Label position" to "High".

Fig 6 — Do not delete the primary axis. To hide the labels, set the font to white. Remove the last point from the "Data" series. You can remove this point either with the mouse, by moving the cell selection grab-handle, Fig 7, or by editing the series with the Select Data...>>Edit series dialogs, Fig 8.

 

[thisoldman]

I read the original post again. If you wish to have a blank position at the horizontal right end, simply start with this data:

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]Data[/TD]
[TD]Dummy[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]150[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]160[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]170[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]180[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]190[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]210[/TD]
[TD][/TD]
[TD="align: right"]35[/TD]
[/TR]
</tbody>[/TABLE]