Array formula.(Microsoft Excel)
Array formulas are less well known, but are a powerful feature available in Excel 2007 or later.
When used properly, array formulas can simplify a sheet by omitting intermediate calculation cells and formulas.
Although powerful, it also has many disadvantages, so you need to consider the trade-offs when adopting it.
In Excel 2019 and later, a more convenient feature called Spill has been added that eliminates many of the disadvantages.
This is recommended over array formulas, and Microsoft has a policy of moving to it.
Use Cases and Benefits.
Common formulas in a single cell formula.
For example, if a table has a quantity and a price, and you want to multiply them together to get the amount, the traditional formula is realized by copying the "quantity * price" formula down.
In the case of array formulas, after selecting a range of amounts,
If you specify a range of quantity and price and press [Ctrl]+[Shift]+[Enter], the special formula surrounded by {} will be applied as a common formula to all cells in the selected range.
The range of array formulas will all be the same formula.
This has the advantage of making the sheet more secure, as it eliminates the possibility of forgetting to copy formulas or destroying only partial cells.
It also eliminates the need for absolute cell reference, which can be difficult for unfamiliar people to use when calculating rates.
However, there are many disadvantages that should be considered.
First, it is not possible to add or delete columns or rows that would change the range of the array formula.
Also, since you cannot leave the input mode without pressing [Ctrl]+[Shift]+[Enter]
However, with Spill, this drawback is eliminated.
Reduction of intermediate tally cells (e.g., total amount after unit price x quantity)
For example, when calculating the total amount after multiplying the unit price and quantity, it is common to create an intermediate calculation cell with the multiplication for each row.
The array formula can be used to calculate the total without creating this Amount column.
In the array formula, the following formula is written in cell C2.
=SUM(Price cell range * Quantity cell range)
Note that the two cell ranges must be the same size.
If you press [Ctrl]+[Shift]+[Enter] in this state, the formula is enclosed in {} and becomes an array formula.
The result will be multiplied by the unit price and quantity, respectively, and then summed.
(Same as the result using the intermediate formula cell)
This allows the total sales amount to be produced without creating unnecessary cells. This makes the formulas and the sheet simpler and makes the sheet more concise.
Note that {} is not an array formula even if it is entered by keyboard.
It must be confirmed by pressing [Ctrl]+[Shift]+[Enter].
Aggregation of those matching the criteria.
In environments older than Excel 2016, there are COUNTIF, SUMIF, and AVERAGEIF functions,
However, the MAX and MIN functions do not have the ability to specify conditions.
Therefore, it is necessary to use the IF function to create cells with specified conditions, and then use the MAX function or MIN function to tabulate the data.
In the same example as before, the maximum Amount of Food only is calculated.
In this example, the IF function is used in column F to calculate the sales amount for Category "Food" only, and the MAX function to column F is written in cell C2.
Using an array formula, this would result in a formula like this.
=MAX(IF(C5:C10="Food",D5:D10*E5:E10,0))
With this formula entered, press [Ctrl]+[Shift]+[Enter] to confirm the cell entry.
The same result is calculated in one cell as if you had created an intermediate calculation cell.
Of course, the MIN function can also be used.
However, since Excel 2016, the MAXIFS and MINIFS functions have been added. functions have been added, so it is more appropriate to use those functions.
Specifying a Cell Range for a Single Cell Argument
Functions that allow only a single cell to be specified can be used with a range of cells to simplify the description.
Functions that require array formula.
Disadvantage
Range of array formulas impossible to mix different values/formulas
It is a trade-off for safety, but it is impossible to edit the flexibility to include different formulas or fixations only in certain rows of the sales amount.
Impossible to add or remove columns or rows that affect the range of an array formula
Because of this, it can only be used on sheets with fixed cell configurations.
Few people know about array formulas.
Array formulas, while useful, are a little-known feature, so people do not know they need to press [Ctrl]+[Shift]+[Enter], and if someone other than the sheet creator sees them, they may not know what they are doing and may destroy the formula.
Formulas are fragile.
If you enter input mode for a cell with an array formula and leave it, the array formula is canceled and an error occurs.
You must [Ctrl]+[Shift]+[Enter] to exit the cell each time, even if you have not changed anything.
It should be noted that this is very fragile, including the previously mentioned unknown.
Many of the disadvantages can be eliminated with spill
Many of the disadvantages can be eliminated by the new Spill feature.
We recommend using Spill, although it is limited to Excel 2019 or later or Office365.
---