(Showing Draft Content)

This function returns a single value, a single cell range or an error using the intersection logic. There are two types of intersection logic - Implicit Intersection and Explicit Intersection.

The Implicit intersection logic selects a single value from an array of values while also ensuring that the formula returns only one value that the cell can hold. Implicit intersection can be used when users want to specify a range argument to a function that expects a single value and the formula is not an array formula (a formula entered using Ctrl+Shift+Enter). In this case, the value in the cell of the range which intersects the column or row of the formula cell is used for the function.

When dynamic arrays are enabled, then the "Implicit Intersection" is not supported, and users must use the SINGLE function (or the '@' operator) to specify the "Explicit Intersection" in order to return the single value. This is required because specifying the range argument will pass the range to the function and the results will be spilled as a dynamic array.

type=note

SINGLE is a dynamic array formula, and you need to enable the dynamic array feature in the Workbook.

Syntax

SINGLE(value)

Arguments

value - [required] Specifies the value that you want to evaluate using implicit intersection.

Data Types

Accepts values in the form of a cell range. Returns a single value, a single cell range or an error.

Remarks

If the argument provided by the user contains a range, then the SINGLE function returns the cell at the intersection of the row or column of the formula cell. But, if there is no intersection, or more than one cell falls into the intersection, then this function will return a #VALUE! error. Further, if the argument provided by the user contains an array, the SINGLE function returns the first item (i.e. Row 1, Column 1).

Examples

For instance - The cell A15 in the following image contains the formula =SINGLE(A15:E15) and returns the result "C" in the cell C16 by evaluating the intersection of the rows and columns in the cell range A15 to E15.

Version Available

This function is available in SpreadJS 13 or later.