Array Functions

This category contains the array functions.
What is an Array?
An array is a linked range of cells on a spreadsheet containing values. A square range of 3 rows and 3 columns is a 3 x 3 array:

 ABC
173133
295172
351050

The smallest possible array is a 1 x 2 or 2 x 1 array with two adjacent cells.

SUMPRODUCT

  • Multiplies corresponding elements in the given arrays, and returns the sum of those products.
    Syntax
    SUMPRODUCT(Array1; Array2; …; Array30)
  • Array1, Array2; …; Array30 represent arrays whose corresponding elements are to be multiplied.
  • At least one array must be part of the argument list. If only one array is given, all array elements are summed.
Example
 ABCD
12345
26789
310111213

 =SUMPRODUCT(A1:B3;C1:D3) returns 397.

Calculation: A1*C1 + B1*D1 + A2*C2 + B2*D2 + A3*C3 + B3*D3
You can use SUMPRODUCT to calculate the scalar product of two vectors.

MMULT

  • Calculates the array product of two arrays. The number of columns for array 1 must match the number of rows for array 2. The square array has an equal number of rows and columns.
    Syntax
    MMULT(Array; Array)
  • Array at first place represents the first array used in the array product.
  • Array at second place represents the second array with the same number of rows.
Example

Select a square range. Choose the MMULT function. Select the first Array, then select the second Array. Using Function Wizard, mark the Array check box. Click OK. The output array will appear in the first selected range.

TRANSPOSE

  • Transposes the rows and columns of an array.
    Syntax
    TRANSPOSE(Array)
  • Array represents the array in the spreadsheet that is to be transposed.
Example

In the spreadsheet, select the range in which the transposed array can appear. If the original array has n rows and m columns, your selected range must have at least m rows and n columns. Then enter the formula directly, select the original array and press Shift + Ctrl + Enter. Or, if you are using the Function Wizard, mark the Array check box. The transposed array appears in the selected target range and is protected automatically against changes.
     

 ABCD
12345
26789

The above table is 2 rows, 4 columns. In order to transpose it, you must select 4 rows, 2 columns. Assuming you want to transpose the above table to the range A7:B10 (4 rows, 2 columns) you must select the entire range and then enter the following:
TRANSPOSE(A1:D2)
Then make sure to enter it as matrix formula with Shift + Ctrl + Enter. The result will be as follows:
   

 AB
126
237
348
459
Text Functions This section contains descriptions of the Text functions ...
Read More
Array Functions This category contains the array functions.What is an ...
Read More
Statistical Functions COUNT Counts how many numbers are in the ...
Read More
Mathematical Functions Mathematical This category contains the Mathematical functions for ...
Read More
LibreOffice Calc Logical Functions This category contains the Logical functions ...
Read More
Date & Time Functions TODAY Returns the current computer system ...
Read More
Database Function Parameters A B C D E 1 Name ...
Read More
Information function in calc CELL Returns information on address, formatting ...
Read More
English Hindi