Frequency Bins

**John Bates** · 05-07-2010, 01:06 PM

Hi Friends,

I have a lot of complicated data. In one specific column, I have about 10,000 rows of numeric data that I would like to place into some frequency bins. I am familiar with the Data Analysis addin option for binning, but i need something that is more dynamic since this data changes daily.

I am looking for a formula that will help me determine the optimal number of bins that I should include. For example, in a column of data I have numbers ranging from 0 - 1545 with the median being 681 and the mode is 598. Any thoughts on how to determine how bins I need? Once I have this, then i will use the FREQUENCY() array to calculate the frequency for each bin.

Thanks,
John

**tony h** · 05-10-2010, 07:28 AM

Surely the number and range of bins would depend on what you were trying to determine from the data. You have not said what the data is nor what you are trying to achieve.

**sweep** · 05-10-2010, 07:36 AM

if you're creating a histogram, you could use several methods. The one that excel uses is effectively = SQRT(COUNT(Data))

**John Bates** · 05-13-2010, 11:14 AM

Thank you for your help on this. I spoke with a statistician about being able to do this and there really isn't a rule for it so I'm going to have to take a different avenue. Thanks again for all your help!!