Excel Guideline #9: Array Functions Are Past Times

Like Stephen I was horrified by the FAST standard. I think it is actually a product of its environment, reflecting both the skill level of contract developers and the expectations of clients that wish to tinker despite minimal spreadsheet knowledge and, provided it is not put forward as an exemplar of good practice, that is fine by me. To satisfy FAST 3.02-01 'Formulas must be consistent along the axis of presentation' requires that finance models are either list or array problems. The definition of a corkscrew calculation makes order critical so narrows the problem down to being an array problem. I find it perverse that rule 3.03-08 'Do not construct array formula' eliminates the very structure that characterises the problem and would ensure rule 3.02-01 cannot be broken. Power query also assumes uniformity and is efficient for lists so, array formulas are no longer needed in that context. The presence of sort and filter operations confirms that Power query is not intended for arrays though. p.s. I must remember that painting stripes on the cat does not make it a tiger :-)

@mateo mol. I read the FAST standards once and was horrified. As you point out your standards are indeed consistent with FAST as well. I think FAST is OK for people who never studied (nor understood) advanced maths in high school, i.e. matrices and vectors. However on your particular post, where I am still uncomfortable is that you are advising people without defining specifically who your reader is. So I think that you are influencing some people to work well below their capability, based on those they may be forced to work with. That is you are adopting the FAST philosophy - identify who is the least capable in the organisation and make that your standard - it has a certain socialist ring to it. This (appealing to least capable person) is a common problem with Excel training courses and why I decided to start my own course (which is targeted to the most competent person in an organisation) . Anyway... the market is certainly appealing to the lowest level user - so all the best.

Stephen Gay, you are very right. Our guideline is focussed on situations in which several people have to work on the same spreadsheet and not all of them are as professional as we are. I think, most situations are like that. The FAST Standard, for example, advices the same. So, I realize that you experts not always agree from your own perspective. But that's no problem!

In place of the Array formulas, my advise is "Power Query". Previously, when I gave INDEX(MATCH for the 10K+ table, it was calculating too slowly. Array formulas could be used under certain circumstances. Unless, Power Query is the best and beast.