BIN

Syntax:

BIN(data:array, bins:array)

The bins array or range of cells defines a set of bins into which the values from the data array or range of cells is counted.

For example, if bins is {20,40,60,80}, the returned array gives the number of elements of data 20, 40 but >20, 60 but >40, 80 but >60 and finally >80. The returned array will therefore always have one more element than the bins array. Neither data nor bins need be sorted.

One common use of this function is to organise data for making a histogram. For example, with data in A1:A100, and bin categories in B1:B5, the following formula would provide the basis for a bar chart:

SET_VALUE(C1:C6, BIN(A1:A100, B1:B5))

You would then make a bar chart from the range B1:C6, using column B as labels.