93
Miscellaneous functions
CHOOSE(index, values ...)
Chooses a value from a list. If index evaluates to
1, the first value is returned; if it evaluates to 2, the
second value is returned and so on.
Example: CHOOSE(4, “mon”, “tue”, “wed”, “thur”,
“fri”) returns “thur”.
COLUMNS(range)
Returns the number of columns in the range.
Example: COLUMNS($A$8:$K$9) returns 11.
HLOOKUP(key, range, row)
Looks in the first row of the range for the largest
value that is less than or equal to key. Then it
moves down the column and returns the value in
the specified row. The row index starts from one.
The values in the first row (key row) should be
sorted in ascending column order. For alphabetic
comparison, the search is case-sensitive. That is,
the key “Orange” will match “Orange” and not
“orange”.
Example: HLOOKUP( 5, A1:D3, 2) returns “c”
where the content of range A1:D3 in row order is
{2, 3, 5, 8}
{8, b, c, d}
{10, f, g, h}
INDEX(range, row, col)
Uses a row and col index to choose a value from a
range.
Example: Assume C10:E11 in row order contains
the values:
{5, 2, 7},
{8, 4, 9}.
Then INDEX (C10:E11, 1, 3) returns 7 which is
the content of the cell in row 1 column 3 of the
range.
ISBLANK(value)
Returns TRUE if value is blank, and FALSE,
otherwise.
Example: ISBLANK(C13) returns TRUE when
C13 is empty.
ISERR(value)
Returns TRUE if value is any error value except
NA (Not Available), and FALSE, otherwise.
Example: ISERR( NA() ) returns FALSE.
ISERROR(value)
Returns TRUE if value is any error value, and
FALSE, otherwise.
Example: ISERROR( NA() ) and ISERROR(1/0)
both return TRUE.
To Next Page
Loading...
Need help?
General
