HISTBINS - Number of Histogram Bins

1. The input data series may include missing values (e.g. #N/A, #VALUE!, #NUM!, empty cell), but they will not be included in the calculations.
2. The number of bins, $h$, can be assigned directly or calculated from a suggested bin width. $h$
3. $h$ is defined in terms of $h$ as follows:
   
   $$k=\left \lceil \frac{\mathrm{max}(X)-\mathrm{min}(x)}{h} \right \rceil$$
   
   Where:
   
   • $h$ is the input data series
4. Sturges's formula for the number of bins, $h$, is:
   
   $$k = \lceil \log_2 n + 1 \rceil$$
   
   Where:
   
   • $n$ is the number of non-missing values in the input time series data
   
   
   Which:
   
   • implicitly bases the number of bins on the range
   • and can perform poorly for $n \lt 30$
5. The square-root choice for the number of bins, $h$, is:
   
   $$k = \sqrt{n}$$
   
   Where:
   
   • $n$ is the number of non-missing values in the input time series data
   
   
   (This is the bin calculating method that Excel uses for its native histogram)
6. Scott's choice for the bin width, $h$, is:
   
   $$h = \frac{3.5 \sigma}{n^{\frac{1}{3}}}$$
   
   Where:
   
   • $\sigma$ is the standard deviation of the input data series
   • $n$ is the number of non-missing values in the input time series data
7. The Freedman–Diaconis choice for the bin width, $h$, is:
   
   $$h = 2 \dfrac{\operatorname{IQR}(X)}{n^{\frac{1}{3}}}$$
   
   Where:
   
   • IQR is the interquartile range of the input data series
   • $h$ is the input data series
   • $n$ is the number of non-missing values in the input time series data

Example 1:

Files Examples

Related Links

• Wikipedia - Histogram
• Wikipedia - Freedman-Diaconis rule
• MIT - Histogram Bin-width optimization

References

• Balakrishnan, N., Exponential Distribution: Theory, Methods and Applications, CRC, P 18 1996.