Suppose you want to take the average or sum of a large block of numbers after every number in the block has been multiplied by a constant. You can easily combine functions and arrays to perform this kind of operation.

First, select a single cell for the result of the function (assuming the function will return a single result). Type the function as you would normally, and select the cell ranges that you need. When you have done this, instead of pressing Enter, use Ctrl + Shift + Enter.

As an example, the following worksheet calculates the average area of a circle based on a table of many cells, each containing a number representing the radius of a circle. You can calculate the area of a circle by multiplying the square of the radius by the mathematical constant PI. The formula reads

{=AVERAGE(C2:F15^2*PI ())}

In this formula, C2:F15 is the range of cells containing the data. The ^2 raises each cell value (radius) to the second power. The * PI () part, multiplies the squared radius with the Excel PI function and the AVERAGE function calculates an average result. Finally, the curly braces { } enclose the entire function, letting Excel know that this formula involves an array, and that the mathematical operations are to be performed on each cell in the specified range.

The result of all this is, that each and every number in the range C2:F15 will be squared and multiplied by PI and then averaged together for a final result.

You can see the formula for cell H2 in the formula bar. The formula uses the AVERAGE function, a calculation ^2*PI () involving a nested PI function, and finally, is enclosed in { } making it an array formula. (Note: the parentheses () are required after the text PI for Excel to recognize it as the PI function)

The next image shows the formula in cell J2 which is identical except for the lack of curly braces (the formula is not an array formula and causes an error).

Array formulas let you perform mathematical or logical operations with corresponding cells in two or more ranges of the same size. Array formulas also allow you to perform mathematical or logical operations on every cell in a selected range. You can also combine array formulas and functions together to solve awkward problems. Sometimes, using array formulas may be the only solution for a tricky situation.