Array formulas are a powerful feature in Excel that allow you to perform complex calculations on multiple values simultaneously. They can return either a single result or multiple results. This topic will cover the basics of array formulas, how to create them, and practical examples to help you understand their usage.

1. Array: A collection of values in a single row, column, or multiple rows and columns.
2. Array Formula: A formula that can perform multiple calculations on one or more items in an array.
3. Single-Cell Array Formula: An array formula that returns a single result.
4. Multi-Cell Array Formula: An array formula that returns multiple results.

A single-cell array formula performs calculations on an array and returns a single result. To create a single-cell array formula:

1. Select the cell where you want the result.
2. Enter the formula.
3. Press Ctrl + Shift + Enter instead of just Enter. Excel will automatically enclose the formula in curly braces {}.

Suppose you have the following data in cells A1:A3 and B1:B3:

To calculate the sum of the products of corresponding elements in arrays A1:A3 and B1:B3:

1. Select cell C1.
2. Enter the formula: =SUM(A1:A3 * B1:B3).
3. Press Ctrl + Shift + Enter.

The result will be 44, and the formula in the cell will appear as {=SUM(A1:A3 * B1:B3)}.

A multi-cell array formula returns multiple results to a range of cells. To create a multi-cell array formula:

1. Select the range of cells where you want the results.
2. Enter the formula.
3. Press Ctrl + Shift + Enter.

Using the same data in cells A1:A3 and B1:B3, to multiply each element in array A1:A3 by the corresponding element in array B1:B3:

1. Select cells C1:C3.
2. Enter the formula: =A1:A3 * B1:B3.
3. Press Ctrl + Shift + Enter.

The results will be:

The formula in each cell will appear as {=A1:A3 * B1:B3}.

To calculate the average of the products of corresponding elements in arrays A1:A3 and B1:B3:

1. Select cell C1.
2. Enter the formula: =AVERAGE(A1:A3 * B1:B3).
3. Press Ctrl + Shift + Enter.

The result will be 14.67, and the formula in the cell will appear as {=AVERAGE(A1:A3 * B1:B3)}.

To sum the elements in array A1:A3 where the corresponding element in array B1:B3 is greater than 3:

1. Select cell C1.
2. Enter the formula: =SUM(IF(B1:B3 > 3, A1:A3, 0)).
3. Press Ctrl + Shift + Enter.

The result will be 8, and the formula in the cell will appear as {=SUM(IF(B1:B3 > 3, A1:A3, 0))}.

Given the data in cells A1:A5:

Calculate the sum of the squares of the elements in array A1:A5.

Solution:

1. Select cell B1.
2. Enter the formula: =SUM(A1:A5^2).
3. Press Ctrl + Shift + Enter.

The result will be 55, and the formula in the cell will appear as {=SUM(A1:A5^2)}.

Given the data in cells A1:A3 and B1:B3:

Calculate the product of the sums of arrays A1:A3 and B1:B3.

Solution:

1. Select cell C1.
2. Enter the formula: =SUM(A1:A3) * SUM(B1:B3).
3. Press Ctrl + Shift + Enter.

The result will be 99, and the formula in the cell will appear as {=SUM(A1:A3) * SUM(B1:B3)}.

• Forgetting to Press Ctrl + Shift + Enter: Always remember to press Ctrl + Shift + Enter when entering an array formula. If you just press Enter, Excel will not recognize it as an array formula.
• Mismatched Array Sizes: Ensure that the arrays you are working with are of the same size. Mismatched array sizes can lead to errors.
• Using Named Ranges: For better readability and manageability, consider using named ranges in your array formulas.

Array formulas are a powerful tool in Excel that can simplify complex calculations and enhance your data analysis capabilities. By understanding how to create and use both single-cell and multi-cell array formulas, you can perform a wide range of calculations more efficiently. Practice the examples and exercises provided to reinforce your understanding and become proficient in using array formulas.