Ranges, Arrays, And Interpolation

Common-sense functions to ease data manipulation.

RANGES(range1, range2, ...)
Combines and returns discontinuous ranges as a single Range object. See Notes #1, 2.
  • range1, range2, ... : Cell ranges.
Note #1: Used by itself, the RANGES function returns a Range object, which may display as an error. The RANGES function must be used within another function, such as SUM(RANGES(...)).
Note #2: The order of Range objects is irrelevant. For example, RANGES(B1,A1) returns A1:B1.

RANGESIF(ranges, <conditions>, <trim>)
Returns ranges where conditions are TRUE. See Note #2.
  • ranges: Cell ranges.
  • conditions: Array or range of booleans, TRUE (default) or FALSE. See Note #1.
  • trim: Boolean, TRUE or FALSE (default). Removes blank columns and rows from result.
Note #1: Length of conditions must be same length as ranges. If conditions is omitted, then all are considered TRUE.
Note #2: Used by itself, the RANGESIF function returns a Range object, which may display as an error. The RANGESIF function must be used within another function, such as SUM(RANGESIF(...)).
Example:
The examples below demonstrate how array functions can benefit from using RANGESIF.
docrangesif.png

MATRIX(array1, array2, …, ";", array3, array4, ...)
Combines and returns discontinuous arrays as a single array object.
  • array1, array2, ... : Array, matrix, or range of values. See Note #1.
  • ";" : When a semicolon is encountered, a new row of arrays is added.
Note #1: Matrix is composed left-to-right. Each array before a semicolon must have the same number of rows. Each set of arrays between semicolons must have the same number of columns.
docmatrix.png

RESHAPE(matrix, <rows>, <cols>)
Returns two-dimensional array of dimension rows by cols.
  • matrix: Two-dimensional array or range of values.
  • rows: Number of rows (default = 1). See Note #1.
  • cols: Number or columns (default = -1). See Note #1.
Note #1: Setting rows or cols to -1 will cause RESHAPE to calculate the proper dimension (based upon number of items).

INTERPOLATE(matrix, lookuparray, lookupvalue, <fit>)
Returns the interpolated matrix row (if lookuparray is a column) or the interpolated matrix column (if lookuparray is a row). See Note #1.
  • matrix: Two-dimensional array or range of values.
  • lookuparray: Array or range of values used for lookup.
  • lookupvalue: Value to lookup within lookuparray.
  • fit: 1 = linear, ordered (default), 2 = cubic, ordered, 3 = cubic, first match, 4 = cubic, last.
Note #1: To change the return behavior, use TRANSPOSE(lookuparray). To return a specific index, use INDEX(INTERPOLATE(...), index).

Last edited Apr 23, 2014 at 5:07 AM by chadspen, version 5