Array formulas in Excel - Tutorials and examples
An array formula is a formula that can perform multiple calculations of one or more items in an array. Array formulas can return multiple values or a single result. Today Dexterity Software will introduce readers to some common array formulas that are used often.
Array formulas in Excel - Tutorials and examples Picture 1
Example of a multi-cell array formula
You have a detailed list of quantity and unit prices of some items sold in the month:
Array formulas in Excel - Tutorials and examples Picture 2
To calculate the total sales, you must calculate the sales of each item, then add up.
Array formulas in Excel - Tutorials and examples Picture 3
Instead of the above, replace it with the above = SUM (C4: C13 * D4: D13) and press Ctrl + Shif + Enter .
Array formulas in Excel - Tutorials and examples Picture 4
Note: For an array formula, you must use Ctrl + Shift + Enter to end the formula otherwise it will produce an error.
Array formulas in Excel - Tutorials and examples Picture 5
Example of array formulas to create constants
You create the array constant by entering the list of items, then enclosing the list in curly braces. For example, you need to enter the list of Apple, Lemon, Orange. You choose cell A3: C3. In the formula bar, type = {"Apple", "Lemon", "Orange"}. Then press Ctrl + Shif + Enter .
Array formulas in Excel - Tutorials and examples Picture 6
And this is the result:
Array formulas in Excel - Tutorials and examples Picture 7
Some other examples use array formulas
In addition to the SUM function example as above, you can use array formulas with other functions like Left, Right, Mid, Max, Min, If .
LEFT, RIGHT, MID and array formulas
Consider the following example for the data array:
Array formulas in Excel - Tutorials and examples Picture 8
The MAX function uses an array of data
To find the maximum value of AB in the example below:
Array formulas in Excel - Tutorials and examples Picture 9
You must perform two calculations, first you calculate the difference of points A and B, then find the maximum value.
Array formulas in Excel - Tutorials and examples Picture 10
Instead of having to do the above two steps, you use the array formula = MAX (B4: B12-C4: C12) and press Ctrl + Shiff + Enter .
Array formulas in Excel - Tutorials and examples Picture 11
Above Software Tips showed you how to use array formulas in Excel. Good luck!
You should read it
- Instructions and examples of array formulas Array Formulas in Excel
- How to hide formulas in Excel
- MS Excel - Lesson 5: Excel formulas and functions
- Guidelines for importing Chemistry formulas in Excel
- Instructions on how to copy formulas in Excel
- How to create an Excel formula in Notepad ++
- How to color formula cells in Excel automatically
- How to delete quick recipe on Excel
- Array (Array) in C #
- Array (Array) in C / C ++
- How to display formulas and print formulas in Excel
- How to Copy Formulas in Excel