Buzz
Excel array formulas are capable of performing multiple calculations on one or more items within a range. They can return multiple values or a single result. Today, Software Tips will introduce you to some commonly used array formulas.
Example of Multi-Cell Array Formula
You have a detailed table of quantity and unit price for some items sold in a month:
Calculating total sales involves summing the sales for each item individually.
Instead of the above method, you can replace it with the following: =SUM(C4:C13*D4:D13) and press the shortcut keys Ctrl + Shif + Enter.
Note: When using array formulas, you must use the shortcut keys Ctrl + Shift + Enter to finish the formula; otherwise, it will result in an error.
Example of an array formula to create a constant
Create an array constant by entering a list of items, then enclose this list in curly braces. For example, if you need to enter the list Apple, Lemon, Orange, you select cells A3:C3. In the formula bar, enter ={'Apple','Lemon','Orange'}, then press the shortcut keys Ctrl + Shif + Enter.
And here is the result:
Some other examples using array formulas
In addition to the SUM function example above, you can use array formulas with other functions such as Left, Right, Mid, Max, Min, If…
LEFT, RIGHT, MID Functions and Array Formulas
Consider the example below for the data array:
Using the MAX Function with Data Arrays
To find the maximum value of A-B in the example below:
You must perform two operations, first calculate the difference between point A and B, then find the maximum value.
Instead of the above two steps, you use the array formula =MAX(B4:B12-C4:C12) and press Ctrl + Shiff + Enter.
Here, Software Tips has guided you on using array formulas in Excel. Wish you success!
