# 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{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.