HISTBINS - Number of Histogram Bins

Returns the number of histogram bins using a given method.

Syntax

HISTBINS(X, Method)

X

is the input data series (one/two-dimensional array of cells (e.g., rows or columns)).

Method

is a switch to select the calculation method (1 = Sturges's formula, 2 = Square-root, 3 = Scott's Choice, 4 = Freedman-Diaconis choice, 5 = Optimal (default)).

Method	Description
1	Sturges's Formula.
2	Square-root Choice.
3	Scott's Choice.
4	Freedman-Diaconis choice.
5	Optimal (Loss-Function min) (default).

Remarks

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, $k$, can be assigned directly or calculated from a suggested bin width $h$.
3. $k$ 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, $k$, is: $$k = \lceil \log_2 n + 1 \rceil$$ Where:
   • $n$ is the number of non-missing values in the input time series data.
   Note that Sturges's formula implicitly bases the number of bins on the range and can perform poorly for $n \lt 30$.
   • 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, $k$, 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)}{{\sqrt[3]{n}}}$$ Where:
   • IQR is the interquartile range of the input data series.
   • $X$ is the input data series.
   • $n$ is the number of non-missing values in the input time series data.

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.