Array Operation

An array operation is an 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. 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

Array formulas include one or more array operations. The final result of an array formula can be a single value or multiple values.

Array operations and Excel 365

The distinctive feature of an array formula is that it includes an array operation, and there is a big difference in how array operations are handled in the current version of Excel versus older versions. In Excel 2019 and older, many formulas with array operations had to be entered with control + shift + enter to work properly. In Excel 365, arrays are native , and this is not necessary. In Legacy Excel , there are four functions that can natively handle array operations in an argument : SUMPRODUCT , LOOKUP , AGGREGATE , and INDEX *. These functions can be used to create array formulas that work without control + shift + enter in older versions of Excel.

Example

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}

The numbers returned by the CHAR function in Excel come from ASCII. ASCII stands for “American Standard Code for Information Interchange” and is a 7-bit character set that contains characters from 0 to 127.

The original ASCII specification encodes 128 characters into numbers ( see table below ). These include the numbers 0 to 9, lowercase a-z, uppercase A-Z, and punctuation. The first 31 characters are non-printing “control codes”, most of which are no longer used, with the exception of the carriage return (13), line feed (10), and tab (9).

ASCII codes in Excel

Excel contains two functions designed to work with ASCII codes: the CHAR function and the CODE function. To specify a character according to its ASCII number, you can use the CHAR function like this:

=CHAR(65) // returns "A"
=CHAR(13) // carriage return

To return the ASCII number for a character, you can use the CODE function :

=CODE("A") // returns 65

Note: The Unicode character set is widely used these days on the web and in modern applications. Unlike ASCII, Unicode is a standard designed to support all of the world’s languages, and has many thousands of characters. Excel has two functions designed to work with this extended character set: UNICHAR and UNICODE .

ASCII codes

The table below shows standard printable ASCII characters and codes. The second table shows non-printable control codes .

ASCII control codes

The table below shows the first 31 characters in ASCII, which are non-printing control codes. These codes were originally intended to control devices, and are no longer used, with the exception of the carriage return (13), line feed (10), and tab (9).