SPECIALISTS IN FINANCIAL MODELLING
0 item(s) $0.00 AUD + GST

Should we use Array Formulas in everyday models?

21 April, 2009

The array formula is probably one of the most powerful tools in Excel - but also the most under-used.  For some reason it is a tool that is shunned by many - even advanced users - of Excel.  But just because most people don’t know how to use them, does that mean they should be avoided in a financial model?

If you want your model to be user-friendly and able to be used by other people - then, yes.  Let’s face it; having a fantastic model no one else can understand is not really a great achievement.  Heaven forbid that a modeller would put an array formula in a model just to show off their Excel skills!  But surely we shouldn’t have to “dumb down” our models for other people to understand?  It depends on who our audience is.  If the user, (like most of my clients) is an intermediate Excel user who is likely to want to tinker with their model, then it is best to avoid array formulas. 

For most Excel users, coming across the formula {=TRANSPOSE(B1:E1)} is a little perplexing, especially when you can’t edit it.  Array formulas don’t follow the usual rules of an ordinary formula (e.g. “Trace precedents” and “Trace dependents” will work on arrays, but don’t help very much!).

Before we go any further, however, let me firstly explain what all the fuss is about, just in case you’re one of the many Excel users who aren’t at ease with array formulas.  Array formulas are an advanced type of Excel calculation.  An array is basically just a collection of data of the same type that can be treated as a single entity, and an array formula treats the entire array as a single input to the formula.

In order to seriously consider yourself an Excel power user, you should know how to build an array formula - or at least be able to recognise and edit one if you come across it.  Array formulas are a very powerful tool, and there are many advantages to using them.  One of the main advantages is security - nobody can accidentally delete part of the array block when using an array formula. Also, because the data can be manipulated as a whole block and used in the formula as a single unit, it’s a lot harder to make a mistake when building the formula.

You can tell if someone has used an array formula because it includes curly brackets ({) in the formula.  Data tables are a fairly commonly used type of array formula - in fact, I think I need to devote another article to the wonderful world of scenario modelling using data tables.

Advantages of using Array Formulas

  • They ensure consistency because all formulas in the table are exactly the same.
  • A model containing arrays will use less memory and be more efficient.
  • Because it’s not possible to change a single cell on its own within an array formula, it is unlikely that you or someone else will change your formula accidentally.
  • Because array formulas are difficult to understand, it means those with only basic Excel knowledge are less likely to change (and mess up!) your formulas.
  • Array formulas do make it possible to perform some calculations that would otherwise be impossible using ordinary formulas.

Disadvantages of using Array Formulas

  • Although array formulas use less memory, if you use too many large arrays in one model it can slow down your calculation.
  • You cannot use column references (such as “A:A” or “D:D”) in your array formulas.
  • They are difficult to audit for many Excel users and require specialist skills (both an advantage and a disadvantage!).

Simple Array Formula Example

The best way of explaining the concept of an array formula is to see one in action.  Below is a very simple example of an array formula.

1. Create two simple blocks of data (arrays) as below:

2. Highlight the block of cells C1:C5 and type (or highlight) =A1:A5*B1:B5 in the formula bar:

 

3. Don’t press Enter yet! Now, hold down the Control and Shift keys whilst hitting Enter at the same time (CTRL+SHIFT+ENTER).

 

4. The formula will appear in the whole array block. Note that the curly brackets have appeared around the formula.

5. Note that you now cannot make any changes to the array block.

Nb: Array formulas are sometimes referred to as “CSE formulas,” because you press CTRL+SHIFT+ENTER to enter them.   

Comments

1. Roger Says:

April 22nd, 2009 at 1:14 pm edit

I’ll come back here tommorrow and show you a few fancy methods to use the array command for counting values in multiple columns. It’s a fun trick that gives lots of flexability.
Hard to explain to management what the values mean though
2. Uli Says:

April 22nd, 2009 at 4:24 pm edit

Very intresting. Was not aware of this functionality. Used arrys in programming many years ago. Thanks for the info.
3. Chester V Says:

April 22nd, 2009 at 11:03 pm edit

Please show a more complex demonstration. It is difficult to see the value using this simple example. Also, are you aware if there are any limitations or differences with use of array formulas in Excel 2008 for Mac? Thank you.
4. Roger Says:

April 23rd, 2009 at 5:06 am edit

Using the array command like this lets the program count the number of occurrences in the three columns where BB = “Hello” & AB = “Goodbye” & the value in column C > B4 (B4 is variable from 1 to 3 changing the value in B4 changes the total count output). I think in the Excel 2007 there is new command to do the count simpler. Linking the value change into the formula makes the output easily adjustable without manual intervention.

=SUM(IF(CRS!$BB$31:$BB$30000=”Hello”,IF(CRS!AS$31:AS$30000=”Goodbye”,
IF(CRS!$C$31:$C$30000<=$B$4,1,0))))
=SUM(IF(CRS!$BB$31:$BB$30000=”Hello”,IF(CRS!AS$31:AS$30000=”Jambo”,
IF(CRS!$C$31:$C$30000<=$B$4,1,0))))
5. Danielle Stein Fairhurst Says:

April 23rd, 2009 at 8:42 am edit

Thanks for all the helpful comments.
Chester V,
There is some more detail on array formulas, a more complex example plus an Excel tutorial workbook available at: http://www.corality.com/training/tutorials/array-formulas-in-excel
I am not aware of any limitations or major differences when using array formulas in Excel 2008 on a mac. Has anyone had any problems in this area?
Danielle.
6. Shabbir Malik Says:

April 23rd, 2009 at 12:29 pm edit

Array formulae are excellen tool. I have been using them for almost 4 years now. One thing to remember when writing these array formulae is that the range needs to be the same for all the criterias. A different range for different criteria can provide incorrect answer. For example,

{=sum(if(A1:A15=”yes”,if(B1:B15=”Good”,C1:C15)))} will give you correct answer but

{=sum(if(A1:A15=”yes”,if(B1:B15=”Good”,C1:C16)))} will give you a zero or incorrect answer.

Also, as the auther said, too many array formulae in one Excel file can make the calculations extremely slow. Hence if you have a lot of Array formulae in a file then make sure that you set your “Calculations” to manual. Make all the changes you need to make to your spreadsheet then turn the Calculation back to “automatic” or just calculate the sheet at the end.
7. Danielle Stein Fairhurst Says:

April 23rd, 2009 at 1:46 pm edit

Thanks Shabbir, yes range selection is certainly something to be very careful of; not only for array formulas but in everyday formulas such as a SUMIF.
8. John Richter Says:

April 23rd, 2009 at 5:23 pm edit

All interesting stuff. I used to use arrays for even ordinary operations (I had a paternalistic concept to prevent ‘other people’ from messing up my models, but then found I was mostly considerably eroding the efficiency of me, myself, and I modifying my own model. “SERIOUSLY disrupts intentional and regularly required modifications” needs to be listed as a disadvantage, and it isn’t.

This good amount of material, and half defense of arrays, has caused me to jump to drafting an entry for No. 10 on “Good Excel Formulas”, Should we use array formulas? See http://www.fi-mech.com/the+swamp+fox/dispatches/?cat=12 if anyone is interest.

In general, I think we need to ask ourselves, are we trying to be i) ‘Excel power users’ (and do I feel incompetent because I am out of practice with arrays having not used them for 10 years), or ii) good modelers?

Shabbir’s examples above couldn’t be better example of what not to do, and I think the difficulty with arrays. Can any professional suggest this is ‘transparent’.

From the professionals out there that in fact do say we need to dumb things down the maximum extent possible. Didn’t a reasonably bright fellow by the name of Einstein say, “Makes things as simple as possible, but no simpler.”

John Richter
Principal
Financial Mechanics
9. John Richter Says:

April 23rd, 2009 at 5:37 pm edit

By the way, I probably wasn’t sufficiently clear re: the editing disruption. It is only marginally more involved (arguably not at all) to train yourself to pre-select array, F2, and CSE. However, there are two problems with this.

First, unless every formula in your model is constructed this way, you now have two means of sorting formulas: i) normal F2 and copy across; and ii) array editing. Setting up to drive a car with two positions you can choose to use/have for brake is not good design if repeatability, muscle memory, and reduction of error is of any concern.

Second, building up a model is about prototyping. If you want to play around with what/where sort of exception needs to happen at a particular point or condition (probably ultimately using a flag), you would wish to jam - temporarily — something at the point. This is hard with array structures, which have to be ‘broken’ back to regular formulas, if they can be.

Hope that makes some sense.

John Richter
Principal
Financial Mechanics
10. John Richter Says:

April 23rd, 2009 at 6:44 pm edit

Sorry, to monopolize this forum, but you guys have better and more recent experience with arrays. There MUST be a better way to ‘break’ a pre-existing array (i.e. convert to ‘regular formula’ when only reason it’s there is to protect against inconsistency — than what I do. What I do is try to find a spare blank row, take a copy of the first cell in the range, edit it and regular return (to clear array notation), delete the array, and copy cell back in and copying it across.

Surely something better than this mess I presume?
11. #10 Are array formulas bad? | Swamp Fox dispatches Says:

April 23rd, 2009 at 8:13 pm edit

[...] Fox decided to jump the queue (is he a Brit?) on this No. 10 when he noticed a useful discussion at Plum Solutions on arrays. Interestingly, the Fox doesn’t debate too many of ‘the facts’, just [...]
12. Mohit Khurana, CFA Says:

May 4th, 2009 at 10:26 pm edit

I just wanted to point out another application of Array formulas - Not only these can be used in a block of cells, but can also be used in a single cell. What I mean is that a stand-alone cell can also make use of an Array formula. E.g. - for the example given in this article - put this formula in any of the empty cells:

{=count(IF(MOD($S$15:$S$19,2)=0,$S$15:$S$19,”"))}

This formula will calculate the number of cells which has even numbers. Due to limitation of putting up a much better example in this comment box, I am not able to show you the real use of why I am using Array function instead of Countif().

Basically Countif() would not allow you to check condition on one range and perform the count on another (similar to Sumif()), so in such cases array functions really come handy.

I can talk more about these functions and (though I am not an expert, but) I can help solve specific questions if anyone has. At least I can try to. Feel free to contact me at - MohitKhurana@CFA-Forum.com

Cheers,
Mohit
13. Colin Delane Says:

May 25th, 2009 at 1:23 pm edit

Like Mohit, I too have used array formulae in a single cell rather than in blocks. This is primarily where a multiple condition test is required (see Roger’s second comment at #4 above), which the Sumif & Countif functions cannot handle.
The Sumproduct function, which works similar to an array formula, can also be used for this purpose - without some of the disadvantages of array formulae. The trick (or trap?) is to understand how each corresponding item in each condition range is evaluated to 0 or 1 before being applied to the item in the value range.

Happy Excelling
Colin
14. Chris Mishler Says:

July 6th, 2009 at 10:47 pm edit

Speaking of arrays in a single cell, one excellent use of this type of formula is in auditing a typical application similar to the example given of two arrays multiplied and summed. I have found that checking stock option accrual totals, for example, is quickly accomplished in an array formula multiplying price times number of shares when there are hundreds of employees’ transactions in the table. It is a great way of recalculating and may find where someone has “plugged” results.

Submitted by danielle on Thu, 20/08/2009 - 17:52.

Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure. Functions can be used to perform simple or complex calculations.

Submitted by Anonymous on Thu, 10/09/2015 - 01:31.

Post new comment

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>

More information about formatting options

By submitting this form, you accept the Mollom privacy policy.