Excel Glossary - Free Excel Tutorial

April 8, 2023April 8, 2023

Excel Array Construction

This article will talk about how to create one-dimensional array or two-dimensional array by using some functions in Excel. When using array formulas in Excel, we often use functions to construct arrays.

1. Generate Array with ROW or COLUMN Functions

Array formulas often need to use “natural number” as parameters of the function, such as the second parameter of the LARGE function, OFFSET function in addition to the first parameter. Through the manual way to enter a constant array will be more trouble, and easy to make mistakes. Then we can use the ROW or COLUMN function in EXCEL to generate a sequence, this method is very convenient and fast.

The following formula produces a vertical array of natural numbers from 1 to 10.

{=ROW (1:10)}

The following formula produces a horizontal array of natural numbers from 1 to 10.

{=COLUMN(A:J)}

2. Generating two-dimensional Array from one-dimensional Array

Below we will show you how to construct a new two-dimensional array with two columns of data.

a. One-dimensional range rearrangement to generate two-dimensional array

If there is a list of students and the name column of that list contains 10 students’ names, we need to randomly place the students’ names in the name column into the cell range of 5 rows and 2 columns (a new two-dimensional array).

We can use the following steps to randomize the names of students in column B to a 5 row 2 column range, such as the cell range E1:F5.

STEP1# Select the cell range E1:F5

STEP2# Enter the following array formula in the formula bar

=INDEX(B2:B11,RIGHT(SMALL(RANDBETWEEN(A2:A11^0,999)/1%+A2:A11,ROW(1:5)*2-{1,0}),2))

STEP3# Press CTRL + SHIFT +ENTER shortcut keys to convert the above formula into an array formula.

STEP4# You will see that the students’ names have been randomly placed in a two-dimensional range of array.

Let’s see how this array formula works.

=RANDBETWEEN(A2:A11^0,999)

The RANDBETWEEN function is used to generate an array of 10 values, where the elements are random integers between 1 and 1000. Since the elements are randomly generated, the size of the array elements is randomly ordered. The array formula generates an array of random integers as follows.

={484;203;468;525;702;220;13;163;386;54}

=RANDBETWEEN(A2:A11^0,999)/1%+A2:A11

The random integer array generated above is multiplied by 100 and then added to the ordinal array of 1 to 10. This ensures that the last two digits of the array elements are ordinal numbers 1 to 10.

={484;203;468;525;702;220;13;163;386;54}/1%+A2:A11

The above array formula returns the following array:

{48401;20302;46803;52504;70205;22006;1307;16308;38609;5410}

=ROW(1:5)*2-{1,0}

The ROW function generates a vertical array {1;2;3;4;5), and then subtracts a constant array {1,0} to produce a two-dimensional array of 5 rows and 2 columns.

=SMALL()

The result is taken as the second argument to the SMALL function, which sorts the array after multiplication and addition processing. Since the original size of the array is random, after sorting, the ordinal number corresponding to the last two digits of each element is randomly ordered.

=RIGHT()

The RIGHT function is used to extract the last two digits of each element, and the INDEX function is used to return the student’s name in the corresponding position in column B. In this way, the names in column B can be randomly populated into a two-dimensional array of 5 rows and 2 columns.

b. Combining two columns of data to create a two-dimensional array

We can use the VLOOKUP function to query from the right to the left, and we can use the array operation and IF function to swap two columns of data to generate a new two-dimensional array.

Here is an example of how the VLOOKUP function can be used to reverse the query by constructing a new array.

If you have a table of employee information, and you need to find the employee’s number by the employee’s name, you can use the VLOOKUP function in combination with the IF function to construct a two-dimensional array to find the corresponding employee number.

The employee information table is as follows:

To find the employee number by name, the steps are as follows.

STEP1# Select the cell B3

STEP2# Enter the following formula in the formula bar and press Enter

=VLOOKUP(B2,IF({1,0},E2:E11,D2:D11),2,)

STEP3# As you can see, Jerry’s employee number has been found.

Let’s see how the above formula works.

The core part of the formula is IF ({1,0},E2:E11,D2:D11), which uses a horizontal array {1,0} and two vertical arrays to perform operations to achieve the position of the column where the employee’s name and work number are swapped. Its returned memory arrays are：

{"Tom",1;"Jerry",2;"Jacey",3;"Wendy",4;"William",5;"Ocean",6;"scott",7;"charies",8;"ceila",9;"nicole",10}

The VLOOKUP function then queries the employee’s name in the two-dimensional array generated by the IF function and returns the corresponding employee number.

3. Extracting Sub-arrays From Data

In daily work, it is often necessary to extract part of the data from a column and reprocess it. For example, If you want to find out the list of employees who meet the specified requirements in the employee table.

The following describes how to extract some data from a column to form a subarray.

Suppose you have an employee salary table and you want to find out the names of employees whose salary is greater than $2000. The salary table is as follows.

You can refer to the following steps to obtain a list of employees who meet the requirements.

STEP1# First you need to select the cell range E2:E11

STEP2# Enter the following formula in the formula bar

=T(OFFSET(B1,SMALL(IF(C2:C11>2000,A2:A11),ROW(INDIRECT("1:"&COUNTIF(C2:C11,">2000")))),))

STEP3# Press CTRL + SHIFT +ENTER shortcut keys to convert the above formula into an array formula.

Let’s see how the above formula works.

=IF(C2:C11>2000,A2:A11)

First use the IF function to determine whether the salary meets the conditions, if the salary is greater than $2000, then return the employee’s ID, otherwise return the logical value FALSE.

=ROW(INDIRECT(“1:”&COUNTIF(C2:C11,”>2000″)))

The COUNTIF function is used to calculate the number of scores greater than 100 and is combined with the ROW function and INDIRECT function to generate a sequence of natural numbers from 1 to n.

=SMALL(IF(C2:C11>2000,A2:A11),ROW( INDIRECT(“1:”&COUNTIF(C2:C11,”>2000″))))

Use the SMALL function to find the employee number whose salary is greater than $2000 and return the following memory array.

={1;3;5;10}

=OFFSET(B1,{1;3;5;10},)

The OFFSET function extracts the employee’s name from the result returned by the SMALL function and returns the following array of employee names.

={"Tom";"Jacey";"William";"nicole"}

=T(OFFSET(B1,{1;3;5;10},))

Finally, the T function is used to convert the multi-dimensional reference returned by the OFFSET function into a memory array.

4. Extracting Sub-array from a two-dimensional Array

The cell range A1:C10 contains data of text and numeric type, see the figure below.

If you want to extract all the text-based data from the specified cell range A1:C10, then you can use the following array formula.

=T(INDIRECT(TEXT(SMALL(IF(A1:C10>="",ROW(A1:C10)/1%+COLUMN( A1:C10)),ROW(INDIRECT("1:"&COUNTIF(A1:C10,"*")))), "r0c00"),))

Let’s see how the above formula works.

=IF(A1:C10>=””,ROW(A1:C10)/1%+COLUMN( A1:C10))

The IF function is used to determine the type of data in the cell range. If the cell value is the text, then let the cell’s line number multiplied by 100, and then add the cell’s column number, and then return a numeric result; if the cell value is not text type, then return the logical value FALSE.

={FALSE,102,FALSE;FALSE,202,FALSE;FALSE,302,FALSE;FALSE,402,FALSE;FALSE,502,FALSE;FALSE,602,FALSE;FALSE,702,FALSE;FALSE,802,FALSE;FALSE,902,FALSE;FALSE,1002,FALSE}

=ROW(INDIRECT(“1:”&COUNTIF(A1:C10,”*”)))

The COUNTIF function is used to calculate the number of text values in the range of cells A1:C10, and combined with the ROW function and INDIRECT function to generate a series of natural numbers from 1 to n.

={1;2;3;4;5;6;7;8;9;10}

=SMALL(IF(A1:C10>=””,ROW(A1:C10)/1%+COLUMN( A1:C10)),ROW(INDIRECT(“1:”&COUNTIF(A1:C10,”*”))))

The SMALL function is used to extract the position information of the cell where the text is located and return a memory array.

={102;202;302;402;502;602;702;802;902;1002}

=INDIRECT(TEXT({102;202;302;402;502;602;702;802;902;1002}, “r0c00”),)

The TEXT function is used to convert the location information to R1C1 reference style, and then use the INDIRECT function to return to the cell reference.

=T(INDIRECT(TEXT({102;202;302;402;502;602;702;802;902;1002}, “r0c00”),))

Finally, the multi-dimensional references returned by the INDIRECT function are converted to memory arrays using the T function.

5. Fill the Merged Cells by Array Formula

In the merged cells, only the first cell has a value, while the rest of the cells are empty cells. When we work with the data, we may need to fill the empty cells in the merged cells with the corresponding values to meet the needs of the calculation.

The following is a product sales table, we need to fill the empty cells in the merged cells with the corresponding region name. The data table is as follows:

You can fill the data into the merged cells by using the following array formula.

=LOOKUP(ROW(A2:A13),ROW(A2:A13)/(A2:A13>""),A2:A13)

Let’s See How This Formula Works:

=ROW(A2:A13)/(A2:A13>””)

This formula assigns a non-empty cell in column A to the row number of that cell, and returns the error value #DIV/O! for empty cells, and finally returns a memory array.

{2;#DIV/0!;#DIV/0!;#DIV/0!;6;#DIV/0!;#DIV/0!;#DIV/0!;10;#DIV/0!;#DIV/0!;#DIV/0!}

Finally, the LOOKUP function is used to perform a fuzzy search and return the corresponding region name.

6. Convert Two-dimensional Array to one-dimensional Array

Some functions only support one-dimensional array as their arguments, not two-dimensional array. For example, the second argument of the MATCH function, the second argument of the LOOKUP function, and so on. If you want to complete the query in a two-dimensional array, you need to first convert the two-dimensional array to a one-dimensional array.

In the figure below, the cell range A1:C4 is a two-dimensional array, by using the following formula you can return the maximum value less than or equal to 100 in the cell range; LOOKUP function will perform a fuzzy search from a one-dimensional array returned by the SMALL formula, and return the value that matches the conditions.

The formula is as follows.

=LOOKUP(100,SMALL(A1:C4,ROW(1:12)))

Let’s see how this formula works:

=SMALL(A1:C4,ROW(1:12))

Because the cell range is 4 rows and 3 columns, it is a two-dimensional array containing 12 elements. You can generate a sequence of natural numbers from 1 to 12 by using the ROW function. Then use the SMALL function to sort the two-dimensional array and return a one-dimensional memory array. The result is as follows:

={16;60;78.6;79;97;97;99;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}

The LOOKUP function performs a fuzzy lookup by row and ignores the error value #NUM!. Finally, the maximum value less than or equal to 100 is returned, which is 99.

October 20, 2022October 20, 2022

Excel Array Operation

We have all heard of arrays. Depending on the dimensionality, they are divided into one-dimensional arrays and two-dimensional arrays. Depending on the data type, they can also be divided into numeric arrays, logical arrays, and so on. The concatenation and operation of different arrays is an important part of excel formulas. Many people are still confused about the arithmetic operations of arrays. This article will introduce the basic operations between arrays, such as addition and multiplication.

Direct operations are performed on arrays by using operators instead of using functions. Since the components of arrays include numeric, textual, logical, and error values, arrays inherit the arithmetic properties of all types of data (except error values). Numeric and logical arrays can perform regular arithmetic operations such as addition, subtraction, multiplication, and division, while text arrays can perform concatenation operations.

Direct Operation Between Arrays and Values

Users can perform an arithmetic operation between an array and a number. The operation returns an array in the same dimension. This array contains the same number of elements. For example, if an M*1 array and a number are added, the new array is also an M*1 array.

See the example below. For vertical array, elements are separated by semicolon, and for horizontal array, elements are separated by comma.

Array: {1;2;3}

Value: 4

Operation: {1;2;3}+4

Result: {5;6;7}

The gif shows the operation between a vertical array {1;2;3} and a number {4}. The new array is also a vertical array contains three numbers {5;6;7}.

In some versions of excel, the area where the new array is to be saved should be selected in advance, then enter the operation in the first cell, and after entering the operation, press Shift+Control+Enter to return to the new array. However, in some versions, such as office 365, the user can enter the operation directly in E2 and then press Enter and the new array will be saved correctly in E2: E4.

The formula operation process is shown in the figure:

Direct Operations Between Two One-dimensional Arrays with Same Direction

For one-dimensional array, based on the directions we can divide array into one-dimensional vertical array and one-dimensional horizontal array.

When two one-dimensional arrays are operated directly, the elements in the same position will be operated correspondingly, and a new array is generated after the operation, which has the same direction and the same number of elements as the original array.

See the example below.

Array1: {1;2;3}

Array2: {4;5;6}

Operation: {1;2;3}+{4;5;6}

Result: {5;6;9}

The formula operation process is shown in the figure:

Here we should note that two one-dimensional arrays should have the same number of elements, otherwise some of the returned values will have an error #N/A.

See the example below.

Array1: {1;2;3}

Array2: {4;5;6;7}

Operation: {1;2;3}+{4;5;6;7}

Result: {5;6;9;#N/A}

Exceeded returns error #N/A. In this example, there is no value in array 1 corresponding to the fourth number {7} in array 2.

In addition to basic addition, multiplication, etc., arrays can be combined into a new array, an application that is widely used in everyday work when searching for something based on multiple conditions. The following example will show you the application in working life.

Example: Query the effort based on two conditions milestone and project.

In F3 enter the formula :

=INDEX(C2:C13,MATCH(F1&F2,A2:A13&B2:B13,0)).

In this example, the two conditions are “Milestone: 3” and “Project: Project B”, these two conditions are saved in columns A2:A13 and B2:B13. We use “&” to concentrate the two texts into one text and the two arrays into one new array. Then the formula uses the MATCH function to search for the location of the set “F1&F2” in the new array “A2:A13&B2:B13”, and the INDEX function returns the effort corresponding to “F1&F2” in C2:C13.

The arrays are as follows:

=INDEX({1200;1000;1100;1500;1200;1300;1400;1300;1000;1100;1500;1400},MATCH("3Project B",{"1Project A";"1Project B";"1Project C";"2Project A";"2Project B";"2Project C";"3Project A";"3Project B";"3Project C";"4Project A";"4Project B";"4Project C"},0))

Operation Between Two One-dimensional Arrays with Different Directions

Array1 is a vertically oriented array, it has one column but M rows, so it is an M*1 array with elements listed in each of the M rows; Array2 is a horizontally oriented array, it has one row but N columns, so it is a 1*N array with elements listed in each of the N columns.

The vertical array M*1 and the horizontal array 1*N are operated directly as follows: each element of array 1 is operated separately from each element of array 2, returning a two-dimensional array of M*N.

See the example below.

Array1: {1,2,3}

Array2: {4;5;6}

Operation: {1,2,3}+{4;5;6}

Result: {5,6,7;6,7,8;7,8,9}

The formula operation process is shown in the figure:

Operation Between One-dimensional Array and Two-dimensional Array

If a one-dimensional array and a two-dimensional array have the same dimensions in the same direction, for example the two-dimensional array is M*N, the one dimensional array is M*1 or 1*N, then they can be operated directly as follows: elements inside each array are operated correspondingly to form a new two-dimensional array of M*N.

See the example below.

Array1: {1;2;3}

Array2: {1,4;2,5;3,6}

Operation: {1;2;3}*{1,4;2,5;3,6}

Result: {1,4;4,10;9,18}

The formula operation process is shown in the figure:

If the dimensions of the one-dimensional array and the two-dimensional array are different, the result will contain the error value #N/A.

Array1: {1;2;3}

Array2: {1,4;2,5;3,6;1,1}

Operation: {1;2;3}*{1,4;2,5;3,6;1,1}

Result: {1,4;4,10;9,18;#N/A,#N/A}

Operation Between Two-dimensional Arrays

Two two-dimensional arrays with the same dimension can be operated directly on two elements at the same position and return a new two-dimensional array with the same dimension. For example, operating two arrays M*N will form a new array M*N.

See the example below.

Array1: {1,4;2,5;3,6}

Array2: {1,4;2,5;3,6}

Operation: {1,4;2,5;3,6}*{1,4;2,5;3,6}

Result: {1,16;4,25;9,36}

The formula operation process is shown in the figure:

If the sizes of the two two-dimensional arrays involved in the operation do not match, the resulting array takes the largest column and row of the two arrays as the new array dimensions, but the part of the array that exceeds the smaller dimension generates the error value #N/A.

October 16, 2022October 16, 2022

Excel Array Formula

An array formula is a special formula that is converted from a normal formula by the Ctrl+Shift+Enter shortcut.

Excel automatically adds curly brackets “{}” at the beginning and end of an array formula. The essence of the array formula is a cell formula, used to explicitly notify the Excel calculation engine to perform multiple calculations on it.

Multiple calculations are the process of performing the relevant calculations separately and simultaneously on the elements of an array that have the corresponding relationships in the formula.

However, not all formulas that perform multiple calculations must be edited into array formulas. Using arrays in the arguments of functions of array type or vector type that return a single result, these functions can automatically perform multiple calculations without using array formulas, such as the SUMPRODUCT function, LOOKUP function, MMULT function and MODE.

Multi-cell array formula

In a single cell using an array formula for multiple calculations, sometimes you can return a set of results, but the cell can only display a single value (usually the first element of the array results), and can not display the results of the entire group. Using multi-cell array formulas, you can display each element of the result array in a different cell.

Multi-cell array formula example

Here is a sales table, if you want to calculate the sales volume of different salesmen, then you can multiply the unit price of E3:E10 by the quantity of F3:F10.

The steps are as follows:

STEP1# Select the G3:G10 cell area, and enter the following formula in the formula bar (excluding the curly brackets on both sides)

=E3:E10*F3:F10

STEP2# Press Ctrl+Shift+Enter shortcut keys to convert the formula into an array formula

STEP3# You can see that the sales volume of different salesmen can be calculated by an array formula.

This type of formula that uses the same formula in multiple cells and is converted by the Ctrl+Shift+Enter shortcut is called a “multi-cell array formula“.

The above formula multiplies the unit price of each product by the respective sales quantity to obtain a memory array. The memory array is as below:

{450;690;250;931;1131;1421;532;1862}

Then, they are all displayed in the G3:G10 cell area.

Note：The curly brackets "{}" at the beginning and end of an array formula are automatically generated by the Ctrl+Shift+Enter shortcut. If you enter curly brackets manually, Excel will recognize them as text characters, and they will not work correctly as formulas.

Single-cell array formula

Single-cell array formulas are array formulas that perform multiple calculations in a single cell and return a single value.

Single-cell array formula example

If you want to calculate the total sales profit of all products, then you can use a single cell array formula to do the statistics.

You can use the following array formula in cell G12, and then press Ctrl+Shift+Enter to convert the formula to an array formula.

=SUM(E3:E10*F3:F10)*G1

The formula first multiplies the unit price and sales of each product, and then uses the SUM function to add up all the elements in the array to get the total sales. Finally, the total sales are multiplied by the profit margin in cell G1, and the returned value is the total sales profit for all products.

As the parameters of the SUM function are numeric types, they cannot directly support multiple calculations, so they must be in the form of an array formula to explicitly inform Excel to perform multiple operations.

The formula in this example can be replaced by the SUMPRODUCT function:

=SUMPRODUCT(E3:E10*F3:F10)*G1

The parameters of the SUMPRODUCT function are the array type, which directly supports multiple calculations, so you can enter the formula as a normal formula and get the correct result.

October 14, 2022October 14, 2022

Excel Array

What is Excel Array?

In Excel functions and formulas, an array is a collection of data elements in one row, one column, or multiple rows and columns. Array elements can be numeric, text, date, logical and error values.

The dimension of the array is the direction of the rows and columns of the array. An array with one row and multiple columns is a horizontal array, and an array with one column and multiple rows is a vertical array. An array with multiple rows and columns has both vertical and horizontal dimensions.

The dimensionality of an array is the number of different dimensions in the array. An array with only one row or column is called a one-dimensional array; an array with two dimensions with multiple rows and columns is called a two-dimensional array.

The size of an array is expressed by the number of elements in each row and column of the array.

A one-dimensional horizontal array with 1 rows and N columns has a size of 1xN
A one-dimensional vertical array with 1 column and N rows has a size of Nx1
A two-dimensional array with M rows and N columns has a size of MxN

Excel Array Types

Constant Array

Constants arrays are string expressions that are written directly to the array elements in a formula and are identified by curly brackets “{}” at the beginning and end.

Constant arrays do not depend on the cell range, can be directly involved in the calculation of the formula.

Constant array elements can not be functions, formulas or cell references. Numeric constant elements can not contain dollar signs, commas and percent signs.

One-dimensional Array

The elements of a one-dimensional vertical array are separated by a colon “:“, the following is an array of numeric constants of size 5x1.

={10;20;30;40;50}

The elements of a one-dimensional horizontal array are separated by a comma “,”, and the following is an array of numeric constants of size 1×5:

={10,20,30,40,50}

Note: For text-based constant arrays, each element in the array is identified by quotation marks by default.

Two-dimensional Array

The elements of a two-dimensional array are separated by a semicolon “;” on each row and a comma “,” on each column.

The following is a 4×3 two-dimensional array of mixed data types containing numeric, text, date, logical, and error values.

10	20	30
A	B	c
#N/A!	#REF!	#NUM!
TRUE	FALSE	TRUE

={10,20,30;"A","B","c";"#N/A!",#REF!,#NUM!;TRUE,FALSE,TRUE}

The process of manually entering a constant array can be tedious, you can use cell references to simplify the input of constant groups, the steps are as follows:

STEP1# Enter the value of the array element in the cell area, such as A1:A4

STEP2# Enter the formula =A1:A4 in cell A5

STEP3# In the formula bar, select the above formula and press F9, the formula can be converted to a constant array

Array in Excel Range

Range array is actually a formula directly referenced in the cell range, the size of the array and the size of the constant is exactly the same. For example, the following formulas A1:A4 and B1:B4 are range arrays.

=SUMPRODUCT (A1:A4*B1:B4)

Array in Memory

A memory array is an array temporarily formed in memory by multiple values returned by a formula calculation. Memory arrays do not have to be stored in the cell range, and as a group can be directly nested in other formulas to continue to participate in the calculation. For example:

{ =SMALL(A1:A4,{1,2,3})}

In the above formula, {1,2,3} is a constant group, and the entire formula results in a memory array of 1 row and 3 columns consisting of the smallest 3 numbers in the range of cells A1:A4.

Here is the array in memory.

={10,20,30}

The difference between memory array and area array：

Range array is obtained by cell range reference, memory array is obtained by formula.
Range Array depends on the referenced cell range, the memory array exists independently in memory.

Name an array constant

A named array is a constant array, a range array, or a memory array defined using a named formula (i.e., a name) that can be called as an array in a formula.

Note: You cannot use constant arrays directly in custom formulas used for data validation and conditional formatting, but you can use named arrays created through the Name Manager.

September 18, 2022September 19, 2022

Excel/Google Sheets: Relative Reference

In a Microsoft Excel spreadsheet or Google spreadsheet, the cell is the smallest element in the worksheet. First of all, the top left corner of the cell as the origin, down to the table for the rows, to the right for the table’s columns, so that it constitutes the coordinate position of the cell in the worksheet. By way of coordinates that the cell in the worksheet address, which is the cell reference.

Cell reference is a cell or cell range address or name in the worksheet. Cell reference has two types of relative reference and absolute reference.

Relative Reference

The default cell reference in Excel or Google Sheets is a relative reference, which is simply a combination of column names and row numbers. When you copy the formula from one cell to another, the relative address of the cell will change according to the relative position of the columns and rows. For example, the relative reference to cell B1 is:

=B1

When you enter the cell reference A1 in cell B1 = A1, when you copy the formula to the right, it will become in turn: = B1, = C1, = D1, etc., when you copy the formula down, it will become in turn: = A2, = A3, = A4, etc.

EXAMPLE

If you want to add the values in columns A and B, and the results will be displayed in column C, this time, we can use the relative reference to the cell. The following is a step-by-step guide.

Step1: In cell C2, enter the following relative reference summation formula =A2+B2, and then press the Enter key

Step2：In cell C2, drag the Fill Handle until cell C6, you will see the addition operation between cells A2 and B2, A3 and B3, etc.

Step3: You can click on any cell in C2:C6 to see the relative reference of the formula, you can see which cells do the addition operation between the cells.