Using Array Formulae

Instructions: You will enter in a normal looking formula from the keyboard, but the method of confirming the entry will be different.

Comment: Array formula are necessary for certain functions that produce tabular output - matrices for example - but they can also be used for normal formulae when there is a repetitive sequence of calculations to perform.

Activity 10.1 Scroll down to the range A23:G27. This is a table identical to the three we have been working on, but this table will summarise the information from the other three tables. To do this we will enter in an array formula.

An array formula is entered into one cell but displays its results in a range of cells. We therefore have to select this range before we enter the formula. Select the range C23:E26 using the mouse. Note that the cell C23 is still white on a black background because it is the active cell in selected range. Now type the following:

=Jan+Feb+Mar

Press <Enter>. The cell C23 displays the error message #VALUE! indicating that what you have typed does not make sense as a formula. This is because we have tried to enter this as a normal formula. We will now re-enter it as an array formula.

Activity 10.2 Reselect the range C23:E26, and click on the formula bar as if you were going to edit the formula. Now hold the <Shift> key and the <Ctrl> key down together, and press <Enter>. The highlighted range will fill with numbers. In the formula bar the formula should have curly brackets around it to indicate that it is an array formula. Move the active box around the range using the cursor keys. You will notice that the formula looks identical in every cell. In reality it is only stored in cell C23.

What this formula has done is add the three ranges together cell by cell according to position. So, it has added:

C5+C10+C15 and displayed the result in C23

C6+C11+C16 and displayed the result in C24

D5+D10+D15 and displayed the result in D23

....

Array formula are necessary for certain functions that output a table of results. The advantages in using them for repetitive calculations in normal formulae are:

quicker to type in

quicker to calculate

take up less storage space

The only disadvantage of using them is that array formulae can be a little harder to understand than normal formulae. It is recommended that you only tackle array formulae when you are confident using normal Excel formulae.

OSU | CoF | Community | Search | Forestry Computing Helpdesk