Array formula is a powerful tool in Excel that allow you to perform complex calculations on multiple values at once. You can use them to find the average of a range of cells, find the maximum or minimum value in a range, or even create a custom function.
What is an array formula?
An array formula is a formula that is entered into a range of cells, rather than just a single cell. When you enter an array formula, Excel will automatically add curly braces {} around the formula, to indicate that it is an array formula.
How do I create an array formula?
To create an array formula, follow these steps:
- Select the range of cells where you want the formula to appear.
- Type the formula into the first cell of the range.
- Press
Ctrl+Shift+Enter.
What are some examples of array formulas?
This chapter helps you understand array formulas in Excel. Single cell array formulas perform multiple calculations in one cell. Here are some examples of array formulas:
Without Array Formula
Without using an array formula, we would execute the following steps to find the greatest progress.
1. First, we would calculate the progress of each student.
2. Next, we would use the MAX function to find the greatest progress.
With Array Formula
We don’t need to store the range in column D. Excel can store this range in its memory. Excel’s memory stores a range called an array constant.
1. We already know that we can find the progress of the first student by using the formula below.
2. To find the greatest progress (don’t be overwhelmed), we add the MAX function, replace C2 with C2:C6 and B2 with B2:B6.
3. Finish by pressing + + .
Note: The formula bar indicates that this is anĀ array formula by enclosing it in curly braces
{}. Do not type these yourself. They will disappear when you edit the formula.
Explanation: The range (array constant) is stored in Excel’s memory, not in a range. The array constant looks as follows:
{19;33;63;48;13}
This array constant serves as an argument for the MAX function, yielding a result of 63.
4. If you’re using Excel 365 or Excel 2021, finish by simply pressing Enter. Bye bye curly braces.
F9 Key
When working with array formulas, you can have a look at these array constants yourself.
1. Select C2:C6-B2:B6 in the formula.
2. Press .
That looks good. Semicolons separate elements in a vertical array constant. Commas separate elements in a horizontal array constant.
What are the benefits of using array formulas?
Array formulas offer a number of benefits over regular formulas. Here are a few of the benefits:
- They can perform complex calculations on multiple values at once.
- To create custom functions array formulas can be used.
- They can be used to automate tasks.
- They can be used to save time and effort.
Conclusion
Array formulas powerfully perform complex calculations on multiple values at once. They save time and effort and automate tasks. If you are not familiar with array formulas, I encourage you to learn more about them. They can be a valuable asset to your Excel skills.
| Next Chapter: Sort |
