Excel Array Formula Video Tutorial

In Excel, array formulas allow you to perform powerful calculations on one or more sets of values. The result may fit into a single cell, or an array. Arrays are just lists or ranges of values, but an array formula is a special type of formula that must be entered by pressing Ctrl+Shift+. The formula bar will display formulas enclosed in curly braces {=…}.

Array formulas are frequently used in data analysis, conditional sums and lookups, linear algebra, matrix math and operations, and more. New Excel users may encounter array formulas in other people’s spreadsheets, but creating array formulas is often an intermediate-to-advanced topic.

Excel Array Formula Tutorial Video:

Video tutorial main directory:

1. Enter the array formula

a. When using an array formula, press Ctrl+Shift+Enter instead of just after entering or editing the formula. This is why array formulas are often referred to as CSE formulas.
b. Array formulas will display braces or braces around the formula in the formula bar, like this: { =SUM(A1:A5*B1:B5) }
c. Array constants (arrays “hard-coded” into formulas) are enclosed in curly braces { } and use commas to separate columns and semicolons to separate rows, such as this 2×3 array: {1, 1, 1; 2, 2, 2}
d. If the array formula returns more than one value (multi-cell array formula), first select a range of cells equal to the size of the returned array, and then enter your formula.
e. To select all cells in a multi-cell array: Press F5 > Special > Current Array.

1. Use array constants

Many functions allow you to use array constants like {1,2,6,12} as arguments in formulas. The example I often use in my annual calendar templates returns the weekday abbreviation for a given date. The nice thing about this formula is that you can choose whether to display a single character or two characters.

1. A Simple Array Formula Example

First, I’ll use a very basic example to show how array formulas work. Suppose I have a list of tasks, the number of days each task will take, and a column for percent complete. I want to know the total number of days completed.

Without the array formula, you would create another column called “Completed” and multiply the number of days by the percent complete, then copy the formula down. Then I would use SUM to calculate the number of days completed, as shown in the image below:

1. Enter the multi-cell array formula

Whenever your array formula returns multiple values, if you want to display more than just the first value, you need to select the range of cells containing the resulting array before entering the formula. Doing so produces a multi-cell array formula, which means that the result of the formula is a multi-cell array.

1. Nested IF array formulas

Nested IF array formulas can be very powerful and may be one of the more common uses of array formulas in Excel. Although Excel provides the SUMIF and COUNTIF and AVERAGEIF functions, they do not offer as many degrees of freedom as nested IF array formulas.

1. COUNTIF Alternative: SUM – Boolean Array Formula

Older versions of Excel don’t have the MAXIFS or MINIFS functions, so let’s create our own MAX-IF formula. When we use a hyphen to name a formula, it usually means that we are nesting functions (in this case IF within MAX ).

1. Multi-criteria Boolean Array Formulas

The AND and OR functions only return a single value, even if they contain multiple arrays, so we usually don’t use them in array formulas.
For multi-condition logical array formulas, such as SUM-IF between two dates, you need to perform Boolean logic by adding the Boolean value of the OR condition and multiplying the Boolean value of the AND condition.

1. Array of ordinal numbers (1,2,3,…)

For many array formulas, you will need to use an array of sequence numbers, such as {1;2;3;…n}. You can use the following formula to return an array of serial numbers from 1 to n.

1. Matrix formula: MUNIT, MMULT, TRANSPOSE, etc.

Excel includes some key functions for working with matrices:

MUNIT (m): Creates an identity matrix of size mxm

MMULT (A,B): Multiplies an nxk matrix A with an akxm matrix B using matrix multiplication, resulting in an array of size nxm.

TRANSPOSE (A): Switch rows to columns and vice versa, and can be used not only for numbers.

MDETERM (A): Computes the determinant of matrix A.

MINVERSE (A): Computes the inverse of matrix A (if possible).

INDEX(A,n) or INDEX(A,0,m): Returns the nth row or mth column of matrix A.

10.Other Array Formula Examples

Trendlines in Excel charts allow you to do simple linear regression, but you can also do linear regression in Excel using matrix and array functions. It’s much easier to just use the LINEST function, but for fun I give the general formula for computing the b matrix (least squares estimator) when you have the y and X matrices. Or in other words, if you want to solve b starting from y = X b, you can use the formula b =( X ‘ X ) -1 X ‘ y.

Related:

Windows 11 Pro key Professional Edition retail key