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

Data		Bins
45		0
23		49			Frequency
897		50		49	3
456		99		50	0
123		100		99	0
10		175		100	0
		176		175	1
		299		176	0
		300		299	0
		999		300	0
		1000		999	2
		4999		1000	0
				4999	0
				More	0

<tbody>
</tbody>

 

[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.