Table of Contents
The MAKEARRAY function in Excel allows users to create an array of values that can be used in various formulas and calculations. This function can be used in three main ways:
1) to create a list of sequential numbers, such as for indexing or creating a series of dates;
2) to combine separate arrays or ranges of data into one array for easier manipulation;
3) to dynamically create a range of values based on given criteria, making it useful for data analysis and visualization. By using the MAKEARRAY function, users can easily organize and analyze their data in Excel.
You can use the MAKEARRAY function in Excel to create an array of values with a specified number of rows and columns.
This function uses the following basic syntax:
=MAKEARRAY(rows, cols, lambda(row, col))
where:
- rows: Number of rows in the array
- col: Number of columns in the array
- lambda: A function that specifies the values to fill the array with
The following examples show several different ways to use the MAKEARRAY function in practice in Excel.
Example 1: Create Array with Values Created from Formula
We can type the following formula into cell A1 to generate an array with 5 rows and 3 columns in which the values in each cell of the array are calculated as 2 * row number * column number:
=MAKEARRAY(5, 3, LAMBDA(r,c, 2*r*c))
The following screenshot shows how to use this formula in practice:
The resulting array contains 5 rows and 3 columns.
The values within the array are calculated as: 2 * row number * column number
For example:
- The value in row 1 and column 1: is 2 * 1 * 1 = 2
- The value in row 2 and column 1: is 2 * 2 * 1 = 4
- The value in row 3 and column 1: is 2 * 3 * 1 = 6
And so on.
Example 2: Create Array with Random Text Values
=MAKEARRAY(15,1,LAMBDA(r,c,CHOOSE(RANDBETWEEN(1,3),"Mavs","Hawks","Kings")))
The following screenshot shows how to use this formula in practice:
The resulting array contains 15 rows and 1 column.
The value in each cell of the array is randomly chosen to be Mavs, Hawks or Kings.
Example 3: Create Array with Random Numeric Values
We can type the following formula into cell A1 to generate an array with 10 rows and 2 columns in which the value in each cell of the array is a random integer between 1 and 100:
=MAKEARRAY(10, 2, LAMBDA(r,c, RANDBETWEEN(1,100)))
The following screenshot shows how to use this formula in practice:
The resulting array contains 10 rows and 2 columns.
The value in each cell of the array is a randomly chosen integer between 1 and 100.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: