Exceljet

Quick, clean, and to the point

Array operation

Array operation examples

The distinctive feature of an array formula is that it includes an array operation.

An array operation is some kind of operation that touches or manipulates the values in an array directly. This can be a logical comparison, a math operation, concatenation, or even feeding an array into another function. Array formulas can include one or more array operations. The final result of an array formula can be a single value or multiple values.

In the example shown above, the following formulas appear, each representing a different type of array operation:

=B5:B13>6 // logical comparison
=B5:B13+1 // math operation
=B5:B13&"x" // concatenation
=INT(B5:B13) // another function

For example, the formula in D5 is:

=B5:B13>6

This is an array operation below compares values in the range B5:B13 to 6 with the greater than operator (>):

=B5:B14>6

This is a logical expression with an array that comes from the range B5:B13. Expanded, we have:

{3.3;6.1;9.7;7.2;2.3;6.2;4.9;5.4;9.7}>6

Because there are 9 cells in B5:B13, the result is an array with 9 TRUE or FALSE values:

{FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE}
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables