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
- 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.
- The number of bins, $h$, can be assigned directly or calculated from a suggested bin width. $h$
- $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
- 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$
- 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) - 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
- 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
Examples
Example 1:
|
|
Formula | Description (Result) |
---|---|
=HISTBINS($B$2:$B$30,0) | Sturges's Formula (5) |
=HISTBINS($B$2:$B$30,1) | Square-root Choice (5) |
=HISTBINS($B$2:$B$30,2) | Scott's Choice (3) |
=HISTBINS($B$2:$B$30,3) | Freedman-Diaconis Choice (3) |
Files Examples
Related Links
References
- Balakrishnan, N., Exponential Distribution: Theory, Methods and Applications, CRC, P 18 1996.
Comments
Article is closed for comments.