Excel Array Formula

Definition

An array is a set of units or a set of processed values. If you write an array-based formula, that is, an array formula, you can use this single formula to execute multiple input operations and generate multiple results. Each result is displayed in one unit.

.

Array formulas can be considered as formulas with multiple values. The difference from a single-Value Formula is that it can produce more than one result. An array formula can occupy one or more cells.

The parameter of the array formula is an array, that is, the input has multiple values. The output result may be one, it may also be multiple -- one or more values are elements in the new array obtained by the compound operation of multiple inputs by the formula (can be used as input of other formulas {= sum (round (a1: a50, 2 ))}).

Example

{= Sumif (A2: A9, C2: C3, B2: B9 )}

// {Calculate the total value of Rang (B2: B9) in the row corresponding to the cell whose range (A2: A9) is medium to C2, and calculate range (A2: A9) total number of values in rang (B2: B9) in the row corresponding to cells of medium C3}

{= Sum (if (A2: A11 = "item 1", B2: B11 * C2: C11, 0 ))}

// This array formula creates a condition summation. If the value "commodity 1" appears in A2: A11, the array formula returns B2: B11 and C2: the value corresponding to C11 is multiplied and accumulated. If it is other values, the value is added to zero.

Array formula Input

To enter an array formula, you must first select the cell area (which can be a cell) used to store the results, enter a formula in the editing column, and press Ctrl + Shift + enter to lock the array formula, excel will automatically add braces "{}" on both sides of the formula. Note: Do not enter curly braces by yourself. Otherwise, Excel considers the input as a body label.

Select all areas occupied by the array formula. Select any cell in the area and press Ctrl +.

Principles

An array formula must be input as an array even if it occupies only one cell.