As many as that?single formulae which can be understood by barely 1% of Excellers
The ICAEW regard about 1‰ of users to be developers and I have a feeling that Lori's formulae would leave many of those floundering!
Or full-blown compiled code? That might, though, underrate the value and flexibility of Excel as a development/prototyping environment.In most other respects udfs are the way to go
Much of the problem comes in the context of the final packaging of a solution. The choice may be helper cells or more deeply nested formulae (with the later often requiring array algebra rather than working at the component, cell level). My use of named formulae is a compromise; it reality it is simply building a nested formula but by naming the inner elements it can be understood as a sequence of steps (except for the fact that, for the majority of users, a named formula is absolutely opaque, even when the name itself is informative).
As @Hui points out, helper cells are not always even an option. A large 2D range of helper cells that varies from row to row (in reality, representing a 3D array) is never going to look good on a worksheet! That is not to say that I wouldn't lay out an instance or two of the array while developing and checking the formula. It then has to be packaged until an aggregation operator provides user-intelligible output.