MATOP(range_array_x, oper1, oper2, range_array_y)

Generalization of MMULT.  This function calculates a third matrix by applying two operators, oper1 and oper 2. 

MATOP(A, op1, op2, B); For each k:  MATOP[x, y] = MATOP[x, y] oper2 (A[x, k] oper1 B[k, y])

Range_array_x contains the first matrix.  This argument can take the form of an explicit cell range (such as A1:C3) or the name of a named range. 

Range_array_y contains the second matrix.  This argument can also take the form of an explicit cell range (such as A1:C3) or the name of a named range.

Option 1:  If Oper1 equals either:

  • '+' or 'sum' (addition)
  • '-' or 'sub' (subtraction)
  • '*' or 'prod' (multiplication)
  • '/' or 'div' (division)
  • '^' or 'pow' (exponential)
  • 'ave' (average)
  • 'min' (minimum)
  • 'max' (maximum)

Then Oper 2 must equal either:

  • '+' or 'sum' (addition)
  • '-' or 'sub' (subtraction)

Option 2:  If Oper1 equals either:

  • '=' or 'eq' (equals)
  • '<>' or 'neq' (not equal)
  • '<=' or 'le' (less than or equal to)
  • '>=' or 'ge' (greater than or equal to

Then Oper2 must equal either:

  • 'and'
  • 'or'

 

Examples

Example1:  Given two arrays, array1(in cells A2:B4) and array2 (in cells D2:D3), both containing constant values.

Array1 is a 3 x 2 array (3 rows, 2 columns) and Array2 is a 2 x 1 (2 rows, 1 column).  Therefore, the resulting MATOP matrix will be a 3 x 1 (3 rows, 1 column).

The MATOP function in cell F2 = MATOP(A2:B4, “*”, “+”, D2:D3).  In the MATOP function the "*" operator is used for oper1 and the “+” operator is used for oper2 making the results of this function equal to the MMULT function results. 

F2 is calculated as : A2 * D2 + B2 * D3 = 1 * 7 + 4 * 8  = 39

F3 is calculated as : A3 * D2 + B3 * D3 = 2 * 7 + 5 * 8  = 54

F4 is calculated as : A4 * D2 + B4 * D3 = 1 * 7 + 4 * 8  = 69

Example2:  Given two arrays, array1(in cells A1:A3) and array2 (in cells B1:D1), both containing constant values.

Array1 is a 3 x 1 array (3 rows, 1 column) and Array2 is a 1 x 3 (1 rows, 3 columns).  The resulting MATOP matrix will be a 3 x 3 (3 rows, 3 column).

If cell B2 = MATOP(A2:A4, “min”, “+”, B1:D1), then each cell is calculated as:

B2 = MIN(A2, B1) = MIN(4, 2) = 2

C2 = MIN(A2, C1) = MIN(5, 3) = 3

D3 = MIN(A2, D1) = MIN(4, 6) = 4

B3 = MIN(A3, B1) = MIN(5, 2) = 2

etc. 

Notes: 

The number of columns in range_array_x must be equal to the number of rows in range_array_y. If range_array_x is an m x n matrix, then range_array_y must be an n x p matrix.  The resulting matrix will be an m x p matrix.    

Array 1 and Array 2 must be contiguous ranges. 

All cells within range_array_x and range_array_y must contain numeric data.  Empty cells are treated as 0.