The examples of Array (or CSE) formulas below are based on an awesome article of Microsoft Office Official Website - Click here to see the article
What is an ARRAY?
An array is a collection of items. In Excel, those items can reside in a single row (called a one-dimensional horizontal array), a column (a one-dimensional vertical array), or multiple rows and columns (a two-dimensional array). You cannot create three-dimensional arrays or array formulas in Excel.
An array formula is a formula that can perform multiple calculations on one or more of the items in an array. Array formulas can return either multiple results or a single result. For example, you can place an array formula in a range of cells and use the array formula to calculate a column or row of subtotals. You can also place an array formula in a single cell and then calculate a single amount. An array formula that resides in multiple cells is called a multi-cell formula, and an array formula that resides in a single cell is called a single-cell formula.
Syntax
The primary rule for creating an array formula is worth repeating: Press CTRL+SHIFT+ENTER (That's why they're known as CSE formulas) whenever you need to enter or edit an array formula. That rule applies to both single-cell and multi-cell formulas.
Whenever you work with multi-cell formulas, you also need to follow these rules:
You cannot change the contents of an individual cell in an array formula.
You can move or delete an entire array formula, but you cannot move or delete part of it. In other words, to shrink an array formula, you first delete the existing formula and then start over.
You cannot insert blank cells into or delete cells from a multi-cell array formula.
Examples
For description of these examples or more example - Visit Microsoft Office Official Website
What is an ARRAY?
An array is a collection of items. In Excel, those items can reside in a single row (called a one-dimensional horizontal array), a column (a one-dimensional vertical array), or multiple rows and columns (a two-dimensional array). You cannot create three-dimensional arrays or array formulas in Excel.
An array formula is a formula that can perform multiple calculations on one or more of the items in an array. Array formulas can return either multiple results or a single result. For example, you can place an array formula in a range of cells and use the array formula to calculate a column or row of subtotals. You can also place an array formula in a single cell and then calculate a single amount. An array formula that resides in multiple cells is called a multi-cell formula, and an array formula that resides in a single cell is called a single-cell formula.
Syntax
The primary rule for creating an array formula is worth repeating: Press CTRL+SHIFT+ENTER (That's why they're known as CSE formulas) whenever you need to enter or edit an array formula. That rule applies to both single-cell and multi-cell formulas.
Whenever you work with multi-cell formulas, you also need to follow these rules:
You cannot change the contents of an individual cell in an array formula.
You can move or delete an entire array formula, but you cannot move or delete part of it. In other words, to shrink an array formula, you first delete the existing formula and then start over.
You cannot insert blank cells into or delete cells from a multi-cell array formula.
Examples
Click to enlargeFor description of these examples or more example - Visit Microsoft Office Official Website
Comments