# 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

## Examples

Example 1:

 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
A B
Date Data
1/1/2008 #N/A
1/2/2008 -1.28
1/3/2008 0.24
1/4/2008 1.28
1/5/2008 1.20
1/6/2008 1.73
1/7/2008 -2.18
1/8/2008 -0.23
1/9/2008 1.10
1/10/2008 -1.09
1/11/2008 -0.69
1/12/2008 -1.69
1/13/2008 -1.85
1/14/2008 -0.98
1/15/2008 -0.77
1/16/2008 -0.30
1/17/2008 -1.28
1/18/2008 0.24
1/19/2008 1.28
1/20/2008 1.20
1/21/2008 1.73
1/22/2008 -2.18
1/23/2008 -0.23
1/24/2008 1.10
1/25/2008 -1.09
1/26/2008 -0.69
1/27/2008 -1.69
1/28/2008 -1.85
1/29/2008 -0.98

Formula Description (Result)
=HISTBIN($B$2:$B$30,3,1,2) Center Value of 1st Bin (-1.528)
=HISTBIN($B$2:$B$30,3,2,2) Center Value of 2nd Bin (-0.225)
=HISTBIN($B$2:$B$30,3,3,2) Center Value of 3rd Bin (1.078)

## References

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