Excel array formulas

Previously we’ve looked at Excel’s Sumif, Sumifs and Sumproduct functions, which essentially combine two formulas in one. If you like the idea of regularly doing two things at the same time, it might be worth taking a look at Excel’s array formulas.

Array formulas have been around a long time (before Sumproduct and Sumif) but you won’t find much about them in Excel’s help function. They’re essentially ‘hidden’ inside Excel (perhaps because they’re legacy Excel functionality or they’ve been replaced by new Excel formulas – you’ll have to ask Bill Gates about that one). Cells E19 and G19 below contain examples of Array formulas if you want to get started with these. They’re quite unusual. You MUST press ctrl, shift, enter after you have created or modified the formula. If you just press enter like you normally do they won’t work at all.

Constructing a Sumproduct function using an Array formula

Array formulas: a more complex example

Although you don’t need to use array formulas these days to combine a product with a sum (Sumproduct does that) there might be another occasion where you want to combine two formulas in one. We’ve had people on our financial modelling course who have had a long list of data to manipulate or sort and have wanted to use array formulas. Here’s a more complex example where an array formula is being used to shortcut a compound annual growth rate calculation in Excel. You can click on the picture if you want to download a spreadsheet containing this example:

Array formula

Have a look at cell B8 in the example – it contains the array formula. What’s going on? Perhaps it’s easiest to imagine the array formula working through the list of data from top to bottom, applying Excel functions in order one by one to each piece of data:

  • First Excel takes (1 + the first data point) and multiplies by (1 + the second data point) and so on down the list, until it reaches the end
  • Next, to that total, Excel performs this calculation: ^(1/10) – this is required as part of the compound annual growth rate (CAGR) calculation
  • Next, Excel subtracts the number 1 from the whole total. Again, this is required to calculate CAGR.

The exact calculation may not be important to you. But what’s interesting is that the array formula is working through the list of data from top to bottom applying a series of calculations. You should be able to see that the formula in cell B8 (using the array formula) is a lot shorter and neater than the alternative at cell B7.

Believe it or not, this is a real-life example created for a financial analyst on our modelling course. He had huge spreadsheets with long lists of asset returns – so he was a fan of Excel’s array formula in financial modelling.

About this online financial modelling course material

You’re looking at a free online material covering Excel functions – it’s material that has all arisen from our regular financial modelling course training.

 

 

...join the conversation...

Your email address will not be published. Required fields are marked *