Skip to main content

Min/Max Functions

A minimum or maximum function finds the smallest and largest value of a set of values. Use Min/Max functions with Number data types only (with the exception of the Between function, which also accepts string data types).

Between

BETWEEN(x, min, max): Tests if a value (x) is between 2 other values (min and max), inclusive of min and max. Returns TRUE if x is between min and max. Returns FALSE if x is not between min and max.

  • In order for a value to be considered between 2 values, it must be greater than or equal to the min value, and less than or equal to the max value.

  • All parameters must be a Numeric or String* (including date-time) data type. Blobs and Spatial Objects are not supported. Additionally, all parameters must be in the same data type category. For example, all must be numeric or all must be string/datetime.

  • For String datatypes, the evaluation is case-blind. For example, "cat" is treated the same as "CAT".

*Complex String Comparison

For more complex string comparisons, for example, strings that use wide or half characters or localized dictionaries, results might be unexpected when it comes to dictionary sort order.

We recommend that you use a combination of the CompareDictionary function or the Sort tool with a specific dictionary specified, in order to ensure accurate results. For example:

CompareDictionary(minString, mystr) && CompareDictionary(mystr,maxString)

Example

BETWEEN(2, 2, 4) returns TRUE.

BETWEEN(4.56, 4.4, 4.5) returns FALSE.

BETWEEN("2022-10-31", "2021-01-01", "2021-12-31") returns FALSE.

BETWEEN(DateTimeAdd("13:05:23", 30, "minutes"), "12:00:00", "14:00:00") returns TRUE.

BETWEEN("cat", "CAT", "dog") returns TRUE.

NOT BETWEEN(5, 1, 10) returns FALSE. 5 is between 1 and 10, therefore the expression evaluates to FALSE because of the NOT operator.

Bound

Bound(x, min, max): If (x < min) returns min, else if (x > max) return max, else returns (x).

Example

Bound(6,1,5) returns 5.

Bound(3,1,5) returns 3.

Max

Max(v0, v1, ..., vn): Returns the maximum value from the list.

Example

Max(15, 180, 7, 13, 45, 2, 13) returns 180.

MaxIDX

MaxIDX(v0, v1,..., vn): Returns the 0-based index of the maximum value from the list.

Example

MaxIDX(15,180,7,13,45,2,13) returns 1.

Min

Min(v0, v1,..., vn): Returns the minimum value from the list.

Example

Min(15, 180, 7, 13, 45, 2, 13) returns 2.

MinIDX

MinIDX(v0, v1,..., vn): Returns the 0-based index of the minimum value from the list.

Example

MinIDX(15, 180, 7, 13, 45, 2, 13) returns 5.