How Excel’s dynamic array formulas change the world of financial modelling

There’s been a fair amount of hype about the dynamic arrays rolled out in upgrades to subscription/ 365 Excel. I’ve sat back and watched people messing around with the dynamic arrays online and a big part of me has been impressed by what they’ve come up with.?Part of me has been a little bit horrified at the confusing long formulas that sometimes seem to result.?I realise what I’m saying here today is deliberately controversial (modelling seems to elicit strong opinions amongst modellers on matters that aren’t exactly curing cancer or allowing the human race to forge forwards and colonise new planets - it’s all rather entertaining).?But, just as my eyes start to glaze over when my teenager enthusiastically shows me his new ￡300 smart phone (because I like my old one, I know how it works, and I know it works) I am not sure I very-much like these new-fangled array functions.?But let's take a look...

An introduction: what’s this ‘dynamic array’ stuff all about?

‘Array’ because what you’re doing in Excel is working on a whole set of numbers (usually a range lined up in a row) at the same time.?‘Dynamic’ because it happens automatically.

Perhaps it’s best to look at an example.?Imagine you were working on a set of numbers (E3:P4) below.?Imagine you wanted to link to that set of numbers.?What you’d do is type “=” into cell E4 and link up to E3 (I know I know – you’ve got this).?Then you’d fill right.?Easy.?Nothing new there.

Have a look at what’s going on in E5 below.?Try it for yourself if you haven’t seen it before (if you’re running one of the 365 versions of Excel).?If you type “=” and select the cells E3:P3 all those cells will populate to the right automatically.?It’s dynamic (it happened automatically) and it’s an array.?It’s a… “dynamic array”.

Why does this seem so cool?

Of course you can build a formula using this same technique as well.?And, you’ll probably be starting to think:

• this is neat – I could build all my formulas this way
• they’d fill to the right by themselves
• everyone would be confident that the formula is the same all the way across the row
• this is a much better way to build a model.

And you’d be able to find people out there in interneti-land who agree with you.?But is it really such a great idea??I think you know where I might be going with this.?Everything on the internet is not always as good as it first might appear.?Let’s have a look at building a formula using the new array formulas.?But first…

There’s nothing new (or, at least, very little that’s new) under the Sun

It’s worth noting that we’ve always been able to build formulas this way.?This functionality was always there.?You could have created an array or an array formula in the prior versions of Excel.?What you would have had to do was:

• select rows E3:P3
• type =
• Press “Ctrl shift enter” at the end of the formula (that's what you could do/ had to do in 'old' Excel before the subscription versions and dynamic arrays arrived).

So, before getting too excited about the newness of what we’re seeing, it’s worth remembering that all that’s changed is that arrays have gone from being a bit tricky and requiring a bit of careful thought to create, to being easier to create.?

But I never met anyone who, in the curly bracket array world, ever thought it was a great idea to build a whole financial model or massive sections of a financial model using array formulas.

An example dynamic array formula

Let’s have a look at how you could proceed with your model build using the dynamic array formulas.

Below we’ve got a revenue forecast using regular formulas.?You create the formula in E11 and then you fill to the right:

The dynamic array version of the formula is shown in the next line, row 12.?Hopefully you can spot the difference:

Now the whole array-formula-creation process is definitely a bit more user friendly than it was. You don’t have to enter the old curly brackets, and you don’t have to select all the destination cells (just the one in column E) when you create the array formula.?It will automatically ‘spill’ to the right.

If it can’t ‘spill’ e.g. because you’ve already got some calculations in that row, you will get the new ! error (it may have surprised you – it surprised me the first time it started appearing in my models).?All that error message is telling you is that there’s something in the way stopping the array from filling out to the right.

BUT (and I think it’s a significant but) notice that this array formula, one of the simplest we could create, is longer than its old-school equivalent =$C$9*(1+$C$10)^E1.

I’ve seen people getting pretty excited out there on the internet about dynamic array formulas and some of the resulting complexity scares me a bit.?And it’s all, it seems to me, for the sake of not wanting to take a regular/ simple formula and fill right (“ctrl shift right-arrow, ctrl R”).

You could always do this array stuff in Excel.?You just had to know how to insert funny curly brackets at the end of your formula (“ctrl shift enter” to get them in – see the formula bar below).?Many of us managed to build financial models quite happily without regularly needing to use them or even finding many situations where we wanted to use them.?We tucked them away for the day we were running the “Excel” section of the corporate finance online pub quiz (“What’s an array formula?”).?A mood killer in a late afternoon pub quiz maybe but at least it serves a purpose in giving the Excel nerds a chance to try and demonstrate clear superiority.

Should you reinvent your modelling with dynamic array formulas?

OK OK let’s pretend you’re desperate to model with dynamic array formulas.?Previously we built a revenue forecast (both ‘old school’ line 11 above and with dynamic arrays line 12).?Let’s pretend we’re cracking on with the model build and forecasting gross profit next.

What I’d like you to notice is that the dynamic array formula at line 21 is (I’d say) more complicated than what we’ve got at line 20.?You might say that you can see yourself getting the hang of it but, for most people, line 21 is less fun and line 20 is just plain simpler.?Line 20 is like maths: this times that equals the other.?Line 21 is learning a different language.

(Note the “#” sign is a proper new something – it now serves as a reminder that the formula at line 21 is making use of the array E12:P12.?To create the formula at E21 you type “=”, click on E12 and select through to P12.?But Excel will pretty quickly convert your E12:P12 to E12# to serve as a reminder that you’re working with an array.?Confused??You may be and, if so, you’re probably getting the message: array formulas are more complicated than doing what you know already and have been doing happily for years.?And complexity (as with my home internet network which somewhere in it, I am led to believe, has a TV which now collapses if our broadband goes out) means things are more likely to break and stop working more often than you would like.

Could array formulas be simpler than regular formulas?

Sure “Sum” is three characters versus five for “SumIF”.?But I reckon =SUMIF($E$27:$P$27,E$30,$E$28:$P$28) is probably easier to get your head around than the dynamic array =SUM(($E$27:$P$27=E$30)*$E$28:$P$28).

If you’re a bit unsure how either of them works, at least SumIF comes ready made with instructions should you want to click on the cell that's got the formula in and press “shift F3”.?You’ll get nowhere trying that interrogation trick with the dynamic array version.

Is now the time to reinvent financial modelling?

If you’re one of the modellers in the world who is not completely up to speed with dynamic array functions yet, don’t worry. I don’t think you’re really missing anything for your day-to-day modelling.

If you were to adopt it, it’s a new style of modelling that:

1. feels like it should make your models more error-proof
2. doesn’t make your models more error-proof, if you know how to fill right and also warn people (e.g. with something as simple as a new fill colour) that you’ve done something different in a unique cell formula along the row
3. potentially makes your formulas more impenetrable and more confusing and so makes your models less error-proof
4. if adopted wholesale by financial modellers, potentially makes the world’s models more impenetrable, more confusing and less error-proof, but makes modellers harder to sack.

Conclusion

If you have aspirations to be a fully-fledged Excel financial modeller, don’t worry about dynamic array functions too much.?

There may be some drop dead (wow I always wanted to do that) applications for them somewhere that had never been thought of in the old curly-bracketed array world.?You can relax because I’m sure someone will go “Hey look at this!” and point out for you the one thing you’ve always found a bit awkward in financial modelling but can now suddenly answer much more succinctly using an array (for example, something like this https://www.dhirubhai.net/pulse/ranking-top-10-customers-using-excels-new-functions-mark-robson/). But, remembering the fact we could always make these calculations in the past, you really can’t be missing too much by taking dynamic arrays off your list of things to worry about – instead keeping on trying to model elegantly using simple formulas and simple arithmetic: this times this equals that.

You shouldn’t, and you really don’t want to, reinvent your modelling using dynamic arrays everywhere you can.?As always, where you'll find beauty and integrity is in simplicity.