You can use arrays for a number of different purposes.
Arrays may be used for entering complex numbers and matrices in your worksheet. For example the matrix would be expressed as an array:
{5,4,2;3,4,8}
or if the values were entered in cells A1 to C2 you could express it as A1:C2.
But the cell containing it would display the following:
5
Most matrix and complex number functions return an array as their result.
Arrays can be used to automate data processing and reduce the number of formulae which you have to create. For example, if a worksheet consists of three columns of numbers, and each row needs to be totalled, you could write a SUM formula in each row, or you could use arrays to simplify the process.
If you used arrays you could write the following formula:
A1:A10+B1:B10+C1:C10
This would create an array of ten rows and one column. Each element would be the result of the formula A1+B1+C1, A2+B2+C2, etc. Again you would need to use SET_VALUE to display the results on the worksheet, so your actual formula would look like:
SET_VALUE(D1:D10, A1:A10+B1:B10+C1:C10)
The advantages of this approach are that you save on the memory required to store all the separate SUM functions which you could have used, and all the individual cell references involved. If you inserted a new row between, say, rows 5 and 6, it would automatically be included in the calculation.
The disadvantage is that if you change any element of the data, the whole block has to recalculate rather than just one row. This slows down recalculation.
Arrays can be used as the parameters to a wide range of functions.
Some functions understand arrays and automatically process them. These are the same functions which can process ranges. For example:
SUM(A1)
adds all the elements in the array stored in cell A1.
Functions which do not expect arrays process each array element individually and return those results in an array of the same size as that passed to them. For example:
SIN(A1:A10)
returns the array {SIN(A1), SIN(A2) ... SIN(A10)}. This is referred to as automatic array processing.