HISTBIN - Histogram Bin

Returns the upper/lower limit or center value of the k-th histogram bin.

Syntax

HISTBIN(X, N, k, Ret_type)

X

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

N

is the input number of bins for the histogram.

k

is the bin index or order; e.g., 1 = 1st bin (default),2 = 2nd bin, ..., N.

Ret_type

is a switch to select the return output (0 = lower limit (default), 1 = upper limit of the bin, 2 = center of the bin).

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 lower limit (L) of a bin is calculated as follows:
   $$\operatorname{L}=\operatorname{max}(X) - j \cdot h$$
   $$h=\left \lfloor \frac{\mathrm{max}(X)-\mathrm{min}(X)}{k} \right \rfloor$$
   Where:
   
   • $h$ is the width of the bins.
   • $j$ is the input bin index.
   • $X$ is the input data series.
3. The upper limit (U) of a bin is calculated as follows:
   $$\operatorname{U}=\operatorname{min}(X) - j \cdot h$$
   Where:
   
   • $h$ is the width of the bins.
   • $j$ is the input bin index.
   • $X$ is the input data series.

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.