Array formulas in Excel - Tutorials and examples

An array formula is a formula that can perform multiple calculations of one or more items in an array. Array formulas can return multiple values ​​or a single result. Today Dexterity Software will introduce readers to some common array formulas that are used often.

Example of a multi-cell array formula

You have a detailed list of quantity and unit prices of some items sold in the month:

To calculate the total sales, you must calculate the sales of each item, then add up.

Instead of the above, replace it with the above = SUM (C4: C13 * D4: D13) and press Ctrl + Shif + Enter .

Note: For an array formula, you must use Ctrl + Shift + Enter to end the formula otherwise it will produce an error.

Example of array formulas to create constants

You create the array constant by entering the list of items, then enclosing the list in curly braces. For example, you need to enter the list of Apple, Lemon, Orange. You choose cell A3: C3. In the formula bar, type = {"Apple", "Lemon", "Orange"}. Then press Ctrl + Shif + Enter .

And this is the result:

Some other examples use array formulas

In addition to the SUM function example as above, you can use array formulas with other functions like Left, Right, Mid, Max, Min, If .

LEFT, RIGHT, MID and array formulas

Consider the following example for the data array:

The MAX function uses an array of data

To find the maximum value of AB in the example below:

You must perform two calculations, first you calculate the difference of points A and B, then find the maximum value.

Instead of having to do the above two steps, you use the array formula = MAX (B4: B12-C4: C12) and press Ctrl + Shiff + Enter .

Above Software Tips showed you how to use array formulas in Excel. Good luck!