Computes the bin for a value using the provided lower bounds.

Usage

BinRange(input value, cutoff1, ... )

input value (required) The value to place into the defined bins. Input values can be a number, date or string.

cutoff 1 (required) The lower bounds to compare the value against. Cutoff values must be of the same type as the input value. The first cutoff entered indicates the lower bounds of bin 2. Any value lower than cutoff 1 is placed in bin 1. Bin 2 is inclusive of the entered cutoff.

cutoff 2+ (optional) Additional cutoffs create additional bins. The total number of bins is one more than the number of provided lower bounds.

NOTE: To ensure you get the expected results, make sure your cutoffs are listed sequentially.

Examples

BinRange(25, 50) = 1

Evaluates which bin the number 25 should be placed in. Bins are defined by the cutoff value 50. All numbers lower than 50 are placed in bin 1, all numbers 50 and greater are placed in bin 2.

BinRange(\[Income\], 100000, 500000)

Evaluates all values in the column income, and categorizes them based on the provided lower bounds. Incomes less than 100,000 will be placed in bin 1; incomes 100,000 – 499,999 will be placed in bin 2; incomes 500,000 and greater will be placed in bin 3.

Choose(BinRange(\[Cost\], 50, 100, 200, 500, 1000), "under $50", "$50 +", "$100 +", "$200 +", "$500 +", "over $1,000")

The BinFunction here divides the Category column values into 6 bins, while the wrapping Choose function applies names the bins.

Example of the BinRange function, with Choose function applying bin headings

Related resources