As a spreadsheet modeling expert or analyst, you know your way around a complex formula or two. You’ll also know that efficiency, speed and accuracy are key when working with spreadsheets. So, picking the RIGHT formula for the job is crucial! Our primary focus at SheetRocks, is to make your life easier! Which is why I’m sharing three genius array formula examples you’re gonna want to try out…
What Is an Array Formula?
The main difference between an array formula and a regular formula is how many values are processed at once. With a regular formula, you process one value at a time. But with an array formula, you can process several values at once. This makes an array formula much faster than a regular one because it doesn’t need to evaluate each value individually.
Curly braces ” ” surround array formulas on your spreadsheet. After typing the formula into a cell(s), press the CTRL+SHIFT+ENTER keys to add these to the formula.
Here’s a super simple example: {=SUM(B1:B5*C1:C5)}
There are two types of array formulas: those that perform numerous calculations to generate a single result and those that calculate multiple results.
Let’s look at the difference between the two…
Single-Cell Array Formulas
The most common way to perform a single-cell array calculation is to use the AVERAGE or SUM function to merge the output of the array into a single result. When using the array function, you don’t need the intermediate results to calculate the grand total because it will “remember” each individual calculation without displaying it.
Multiple-Cell Array Formulas
In order to display more than one value from your array formula, you need to choose the range of cells in which the array will be generated. This will produce a multi-cell array formula, which means that the formula’s output will be a multi-cell array.
Here’s an example: {=A2:A6 * B2:B6}
Why Use Array Formulas?
Now that you’re caught up or refreshed on what array formulas actually are, why should you, as a spreadsheet analyst or modeling expert, be using them? Let me tell you!
Saves Time: The most obvious bonus of using an array formula is that it will inevitably save you time in the long run. Once you have your formulas in place, you can let them do all the heavy lifting, so to speak.
Stay Consistent: Having the exact same formula in a table across your spreadsheet is going to guarantee consistency. You will not have to worry if something is amiss or incorrect.
They’re Not For Novices: Array formulas can be complex. For this reason, spreadsheet novices are less likely to change (and mess up) your formulas. You also cannot change a single cell within an array formula, so mistakes and accidents are less likely to happen.
Reduced File Sizes: Spreadsheet files with array formulas will be smaller, freeing up memory. However, if you’re using Excel or Google Sheets, be careful not to use too many in one workbook, or you’ll end up with the whirling circle of doom! If you’re using SheetRocks, on the other hand, it won’t be an issue!
They Make The Impossible… Possible: Simply put, array formulas allow you to complete tasks that normal formulas just cannot achieve. The more formulas you know as a spreadsheet modeler or analyst, the better equipped you are to complete any project or task in the most efficient way.
I’m sold! What about you? If you’re wanting to see how array formulas can help your process, keep on reading as I’m about to dish out three clever array formulas examples that, once you see how they’re done, you’ll be rushing to your computer to give them a try.
3 Clever Array Formulas Examples Using SheetRocks
1. Arithmetic Operation With Each Cell
This array formula example allows you to carry out arithmetic operations such as multiplication, addition, subtraction, and division on each cell, providing multiple calculations.
- Select a cell where you want the results to be displayed.
- Type in EACH A1:B3 + 10 and press Enter
- Here, A1:B3 is your input data and +10 is the desired arithmetic operation to be done with each cell of the data
2. Applying Arithmetic Operation With Subsequent Columns Data
Using this formula, you can combine two (or more) subsequent columns together, calculating multiple equations in a separate column.
- Select a cell where you want the results to be displayed.
- Type in EACH A1:A3 + EACH B1:B3 and press Enter
- Here, A1:A3 and B1:B3 are your required columns whose subsequent columns should be added to display the desired result.
3. Dividing Sum of Table With Each Cell
This clever array formula takes the value of the entire table (in this example 21) and divides it by each individual cell.
- Select a cell where you want the results to be displayed.’
- Type in EACH A1:B3 / SUM(A1:B3) and press Enter
- Here, EACH A1:B1 calculates the value of the whole table. While SUM (A1:B3) returns the summation of each cell value divided by the table SUM
I know you’re itching to give these array formula examples a try, but before you run to open Microsoft Excel or Google Sheets, I want to fill you in on how SheetRocks can help you achieve even more with these new formulas than you could ever expect. Because let’s face it, Excel struggles to handle too many array formulas!
SheetRocks is a powerful spreadsheet tool that allows modeling experts and spreadsheet analysts the ability to perform the most complex calculations without the headache of slow, frozen, or crashing sheets. It was made specifically for YOU to carry out any project or task quickly and efficiently. You can reduce your runtime from hours to seconds and outperform yourself, 100x your spreadsheet performance.
Using these clever array formula examples WILL improve your efficiency. Pairing that with the abilities of SheetRocks will make you unstoppable.
Right now, you can sign up for a free SheetRocks account and get cracking on our 3 array formula examples. What do you have to lose?
