Histogram Bins

[medic49]

I have a set of data that I would like to create bins. For example, 45, 23, and 10 will fall in the 0-49 bin. I created the histogram but am struggling in changing the ranges to display as the bins as the ranges below.


Data:
45
23
897
456
123
10

Bins
0 - 49
50 - 99
100 - 175
176 - 299
300 - 999
1000 - 4999



[TABLE="width: 384"]
<tbody>[TR]
[TD]Data
[/TD]
[TD]
[/TD]
[TD]Bins
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]45
[/TD]
[TD]
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD]
[/TD]
[TD]49
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Frequency
[/TD]
[/TR]
[TR]
[TD]897
[/TD]
[TD]
[/TD]
[TD]50
[/TD]
[TD]
[/TD]
[TD]49
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]456
[/TD]
[TD]
[/TD]
[TD]99
[/TD]
[TD]
[/TD]
[TD]50
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]
[/TD]
[TD]100
[/TD]
[TD]
[/TD]
[TD]99
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]
[/TD]
[TD]175
[/TD]
[TD]
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]176
[/TD]
[TD]
[/TD]
[TD]175
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]299
[/TD]
[TD]
[/TD]
[TD]176
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]300
[/TD]
[TD]
[/TD]
[TD]299
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]999
[/TD]
[TD]
[/TD]
[TD]300
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]1000
[/TD]
[TD]
[/TD]
[TD]999
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]4999
[/TD]
[TD]
[/TD]
[TD]1000
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]4999
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]More
[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]

 

[joeu2004]

I suspect that you are using the Data Analysis tools to create the frequency table and histogram. You are not limited to using that tool.

For the frequency table, create a (bin) range with the values 49, 99, 175, 299, 4999. Call that H1:H5. Select H1:H5, type =FREQUENCY(H1:H4,D1:D1000), where D1:D1000 is the data, and array-enter the formula by pressing ctrl+shift+Enter instead of just Enter. Note that we only need H1:H4. H5 is the "more" bin; it counts anything greater than the H4 bin limit (999), which might exceed 4999.

For the histogram, click Insert > Column Charts > 2-D Clustered Column. You might need to edit the chart in order to make it look the way that you want. You might use the Data Analysis histogram as a guide for the features that you want.