ARRAY FUNCTIONS

An array is simply a series of data, which in a spreadsheet is normally stored in a range of adjacent cells. Excel however can do special things with arrays that may be used as a flexible alternative to the database functions such as DSUM.

Many functions such as SUM or MAX can be combined with the IF function using arrays. This means that the sum or average function will be performed on only those items that meet a criteria. In other words, the values supplied as an argument to the sum function are conditional upon a value in an another column or location. The following example should give you an insight as to the potential uses of array functions.

A table of values (E5:G12) can be interrogated using the criteria values entered into cells F14 and H14 in order to extract summary information. The first query (in cell G16) returns the total number of items processed by operator 'AK' throughout the whole table. The second (G17) gives the highest number of items processed on a day in April (month 4).

{=SUM(IF(G6:G12=$H$14, F6:F12, 0))}
Sum values in F6:F12 where corresponding values in G6:G12 are equal to H14

{=MAX(IF(MONTH(E6:E12)=F14, F6:F12, 0))}
The maximum value in F6:F12 where the corresponding month in E6:E12 equals F14

Both of these are Array formulae - this is evidenced by the curly brackets {braces}. When you enter an array formula there are three rules you must follow:

Would you like to learn more?

• Instead of typing to place the function in a cell, you must type + + . If you don't do so, the formulae probably won't make sense and will cause an error.
• You can not type the Braces {}. They are entered automatically with the above key combination.
• The array ranges (for the criteria and the values) must be the same size (ie the same number of rows) although they need not be in adjacent columns.

These array functions can be used in a range of situations where the criterion ranges required by database functions (eg DSUM) would be unwieldy. In other situations the SUMIF or COUNTIF functions may be better still.

A peculiar use of an array function is to add up a range of numbers which have been entered as text. If a range of numeric values have been converted and formatted using the TEXT function they can no longer be added using the SUM function. The following formula however should do the trick {= SUM(VALUE(range_of_cells)) }. Remember to use + + . In normal use the VALUE function converts a single text number into a numeric value.

The array functions are limited to applying a single criteria range against each calculation. This can be limiting if you want to add up all of the items processed by operator AK during April. Another function called SUMPRODUCT can overcome this limitation.