MATRIX

Information

The MATRIX custom formula is a combination of three build-in formulas from Google Sheets:

The main reason to combine them is that all of them have a way of returning an array (matrix). Combining them allows you to do all (and more...) with just one formula, potentially making things less convoluted.

Do's / Dont's

The MATRIX formula is meant to return a range, but it is not possible to perform ARRAYFORMULA like operations, for example a boolean comparison within the MATRIX range:

=MATRIX(A1:A2=B1:B2)

It is possible to do this, on the other hand:

=MATRIX(ARRAYFORMULA(A1:A2=B1:B2))

Syntax (JsDocs text from script)

/**

 * Create a matrix, that's optionally restricted by row's and column's and optionally sorted by a range of column's

 * @param {A2:B26} range The data to be sorted

 * @param {(number|object|string)} opt_row  Include by inputting a row number (1) or range of row numbers ({1,2,3}),

                                            exclude by turning the include input as a string ("1" or "{1,2,3}")

 * @param {(number|object|string)} opt_col  Include by inputting a column number (1) or range of column numbers ({1,2,3}),

                                            exclude by turning the include input as a string ("1" or "{1,2,3}")

 * @param {(boolean|number)}       opt_sort Use TRUE|FALSE or 1,TRUE,2,FALSE

 * @return The modified range

 * @customfunction

*/

Examples

=MATRIX(A1:C2,,{2,3},2,false)                        // only show columns 2 and 3

                                                     // and sort descendingly by column 2

=MATRIX(ARRAYFORMULA(VALUE(A1:C2)),,{2,3},2,false)   // only show columns 2 and 3

                                                     // and sort descendingly by column 2

=MATRIX(A1:C2,,,false)                               // sort whole range descendingly

=MATRIX(A1:C2,,"3")                                  // exclude column 2 from the range

=MATRIX(A1:Z20,"{1,2,7,8}","{4,8}")                  // exclude row and column series from range

Video

Screenshot

Google Sites

Report abuse