Home / Formula Types / Array

What is an ARRAY formula in a spreadsheet?

Array formulas allow you to perform calculations on multiple values simultaneously within a range. They can process arrays of data and return arrays of results, making it easier to perform complex calculations or operations across multiple cells.

ARRAY formula usage examples.

ARRAY_CONSTRAINT

The ARRAY_CONSTRAIN function is used to constrain an array result to a specified size. It takes three arguments: the input range, the number of rows to include, and the number of columns to include. The function returns a new array that contains only the specified number of rows and columns from the input range.

The BYCOL function groups an array or range by columns by applying a LAMBDA function to each column. It returns the results as a new array.

The BYROW function groups an array by rows by applying a LAMBDA function to each row. It allows you to perform row-wise calculations or transformations on an array or range.

CHOOSECOLS

The CHOOSECOLS function creates a new array by selecting specific columns from an existing range. It takes an array as the input and allows you to specify the column numbers you want to include in the new array. The resulting array will only contain the selected columns, preserving the original row order.

CHOOSEROWS

The CHOOSEROWS function creates a new array from the selected rows in the existing range. It allows you to filter data based on specific criteria and retrieve the rows that meet the criteria. The function takes a data range as the first argument, followed by one or more criteria to filter the rows. The resulting array can be used in various calculations and data manipulations.

The FLATTEN function is used to flatten values from one or more ranges into a single column. It takes multiple ranges as input and returns a single column with all the values concatenated. This function is helpful when we need to consolidate data from different ranges or extract unique values.

FREQUENCY

The FREQUENCY function calculates the frequency distribution of a one-column array into specified classes. It counts the number of values in the data array that fall within each class range and returns an array of frequencies.

The GROWTH function in Excel is used to fit an ideal exponential growth trend and/or predict further values based on known data points. It calculates the growth rate and uses it to estimate future values. The function takes the known y-values (dependent variable) and optional x-values (independent variable) as input, along with optional new x-values for which the function predicts the corresponding y-values. The optional b argument allows for additional constraints on the growth rate.

The HSTACK function is used to append ranges or lists horizontally and create a larger array or merged list. It takes multiple arguments, which can be ranges or lists, and combines them horizontally. The resulting array or list will have the same number of rows as the input ranges or lists, and the columns will be appended in sequence.

The LINEST function is used to calculate various parameters of a linear trend using the least-squares method. It returns an array of values that represent the coefficients of the linear equation that best fits the given data points. The function takes the following arguments: - known_data_y: The array or range of dependent data points. - known_data_x (optional): The array or range of independent data points. If omitted, the function assumes a simple linear regression with the independent variable being the array [1, 2, 3, ...]. - calculate_b (optional): A logical value that determines whether to calculate the y-intercept (b) of the linear equation. If set to TRUE or omitted, the function calculates the y-intercept. If set to FALSE, the function assumes a y-intercept of 0. - verbose (optional): A logical value that determines whether to return additional statistical information. If set to TRUE, the function returns additional information such as the standard error and degrees of freedom. If set to FALSE or omitted, only the coefficients of the linear equation are returned.

The LOGEST function calculates the parameters of the best-fit exponential growth curve based on known data points. It returns an array of values that represent the coefficients of the curve equation. The function can handle both single-variable and multiple-variable data. The optional arguments allow for additional customization and verbose output.

MAKEARRAY

The MAKEARRAY function returns an array of specified dimensions with values calculated by the application of a LAMBDA function. It takes three arguments: 'rows' (the number of rows in the array), 'columns' (the number of columns in the array), and 'LAMBDA' (a function that defines how each value in the array is calculated).

The MAP function is used to iterate over one or more arrays and apply a LAMBDA function to each value. It returns a new array with the transformed values. This function is useful for performing calculations or operations on multiple values simultaneously.

The MDETERM function returns the matrix determinant of a square matrix specified as an array or range. The determinant is a value that represents certain properties of the matrix, such as invertibility and solvability of linear equations.

MINVERSE

The MINVERSE function returns the multiplicative inverse of a square matrix specified as an array or range. The resulting inverse matrix, when multiplied by the original matrix, will yield the identity matrix.

The MMULT function calculates the matrix product of two matrices specified as arrays or ranges. It multiplies corresponding elements in the matrices and returns the resulting matrix.

The REDUCE function reduces an array or range to an accumulated result by applying a LAMBDA function to each value. It starts with an initial value and iterates over the array or range, updating the accumulated result based on the LAMBDA function's logic.

The SCAN function scans an array or range and produces intermediate values by applying a LAMBDA function to each value. It returns an array of the intermediate values obtained at each step.

SUMPRODUCT

The SUMPRODUCT function calculates the sum of the products of corresponding entries in two or more equal-sized arrays or ranges. It is useful for performing calculations that involve multiplying corresponding values and then summing up the results.

SUMX2MY2

The SUMX2MY2 function calculates the sum of the differences of the squares of corresponding values in two arrays, ranges, or columns. It takes two arrays, ranges, or columns as input and returns the sum of the differences of the squares of corresponding values.

The SUMXMY2 function calculates the sum of the squared differences between corresponding values in two arrays, ranges, or columns. It is commonly used to measure the overall discrepancy or variability between two sets of values.

The TOCOL function transforms an array or range of cells into a single column. It takes an array or range as input and returns a single column with all the values from the input. The optional 'ignore' parameter allows you to specify values to ignore during the transformation. The optional 'scan_by_column' parameter determines whether the input should be scanned by column or by row. By default, the function scans by row.

The TOROW function is used to transform an array or range of cells into a single row. It takes three optional arguments: 'ignore' to specify the number of rows to ignore at the beginning, 'scan_by_column' to indicate whether to scan the range by column instead of by row.

The TREND function is used to calculate and predict values based on a linear trend using the least squares method. It takes known y-values, optional known x-values, optional new x-values, and an optional constant 'b' as input. The function returns an array of predicted y-values corresponding to the new x-values.

The VSTACK function is used to append ranges vertically and in sequence to return a larger array. It takes multiple ranges as arguments and stacks them vertically, combining them into a single column or array. This function is particularly useful when you need to consolidate data from multiple sources or when you want to combine data from different worksheets or workbooks.

WRAPCOLS

The WRAPCOLS function wraps the provided row or column of cells by columns after a specified number of elements to form a new array. It takes a range, wrap count, and an optional pad with parameter. The function is useful for splitting data into columns or creating grid layouts.

WRAPROWS

The WRAPROWS function wraps the provided row or column of cells by rows after a specified number of elements to form a new array. It takes three parameters: range, wrap_count, and pad_with (optional). The range parameter specifies the range of cells to wrap. The wrap_count parameter determines the number of elements in each row of the new array. The pad_with parameter is optional and specifies the value to use for padding if the original range does not have enough elements to fill the last row.

We use cookies on our site.