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
- 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 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
- 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:
|
|
| 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) |
Files Examples
Related Links
References
- Balakrishnan, N., Exponential Distribution: Theory, Methods and Applications, CRC, P 18 1996.
Comments
Article is closed for comments.