Exceljet

Quick, clean, and to the point

Pairwise lifting

Example of "pairwise lifting", an array calculation behavior in Excel

The term "pairwise lifting" in Excel refers to a special case of "lifting" – a built in calculation behavior whereby functions that don't handle arrays natively are "lifted" and called once for each value in an array, returning in turn an array containing multiple results.

Pairwise lifting occurs when values in two or more arrays are combined pairwise to produce a different array holding the result of this operation.

Example

The example shown illustrates what happens if you call the SQRT function like this:

=SQRT({1;2;3}+{3;7;13})

Inside SQRT, two vertical arrays are added together with the addition symbol. The values in these arrays are combined pairwise, and result of this operation is a single array with 3 values:

=SQRT({4;9;16})

The SQRT function does not handle arrays natively, so the function is "lifted" and called three times, one for each value. The final result in an array with three values:

={2;3;4}

Handling with multiple results

When lifting occurs in a formula, there will be multiple results, and these need to handled in some way. One approach is to return all values to a worksheet in a multi-cell array formula. Alternatively, you can pass the result into another function like SUMPRODUCT:

=SUMPRODUCT(SQRT(C5:C7+E5:E7))

to return a final result of 9. SUMPRODUCT will handle arrays natively, so this formula does not require control + shift + enter.

The SUM function could be used as well, but would need to be entered with CSE.

For more information on Excel's "array calculation behaviors", see this presentation by Joe McDaid on array formulas.