Calc spreadsheet file used in video: Right-click to download file
Array formulas can handle multiple values simultaneously. Array formulas can also return multiple values. In the example below to multiply each number in columns A and B by 2 and get indiviual results in columns C and D:
The following formula would be typed into Cell C1.
To enter an array formula Press Ctrl Shift Enter and the result will be:
After an array formula is entered it can only be viewed on the input line on the toolbar. The formula is enclosed in curly braces to indicate that it's an array formula:
To edit an array formula first select all the cells which contain the formula either drag and highlight the cells or use the shortcut Ctrl / (on the number pad) to select all the cells.
Then edit the formula in the input line. Enter the formula again by pressing Ctrl Shift Enter
One of the most useful functions made to work with arrays is the SUMPRODUCT function. SUMPRODUCT multiplies the corresponding elements of the arrays and returns the sum of the products.
You do not have to enter SUMPRODUCT with Shift Ctrl Enter. Unlike array formulas it returns a single number so it can be entered into the cell as you would normally enter a formula.
In the example below each number in column A will be multiplied by the Number in column B on the same row. then the results will be added:
Result:
If only one array is given in SUMPRODUCT the results are added. In the following example the each number in column A will be subtracted from each number in column B and the results will be added:
Result:
Frequency is another array function which indicates the frequency distribution of values in a one column array. there are 2 parameters, Data and Classes. The data is the values to be evaluated and the classes represent the limits for the values. The following example shows the values to be analysed in column A the classes in column B are the limits:
The frequency function is entered into the cell by pressing Ctrl Shift Enter
The limits were set at 10,20,25 and the results in column C indicate that there are 4 values in Column A below 10, 2 values which range from 11 to 20, 1 value from 21 to 25 and 3 values which are over 25.
Transpose can be used to rearrange a table so that the columns become rows and vise versa. It takes one parameter, the array of cells to be transposed. Transpose must be entered by pressing Ctrl Shift Enter
The transposed table will be unformatted. The cells can be formatted but the values cannot be changed in the transposed table:
Any changes in the original table will be reflected in the transposed table.