Histogram Function

[iperlovsky]

Does anyone know what the underlying formula/macro is that this function uses
to determine bin size. I have a normal distribution for which I am using a
formalized calculation to determine the bin size, but it is not turning out
to be optimal. I tried using the histogram function and it produces bin
sizes that are ideal. Now I would like to incorporate this process into my
calculation of bins. Any suggestions?

 

[Shane Devenshire]

Hi,

The Analysis ToolPak was written by another company for Microsoft years and
years ago (the 1980's?). You might contact Microsoft, but it may be futile.

Or you might try running a password cracker aganst the add-in and then look
at the underlying code. I sure someone has made a routine to crack add-in
passwords.

 

[Mike Middleton]

iperlovsky -

When you don't specify a bin range for the Histogram tool, Excel uses
INT(SQRT(n)) bins plus one "More" bin.

The first bin is the minimum value of the data set. The step size for
successive bins is

(MaximumValue - MinimumValue)/INT(SQRT(n))

An Excel "bin" value is the inclusive upper limit for an interval. The first
bin contains only the minimum value of the data set, so the frequency for
the first bin is 1.

If you are working with continuous-valued data, you may prefer the histogram
produced by my free Better Histogram add-in, available from
http://www.treeplan.com.

- Mike

http://www.MikeMiddleton.com