Forum Discussion
How to make changes ranges in an array/formula without manually modifying it each time
I think the use of SUMPRODUCT as a wrapper for array formulas is reasonably well known, though some would deny that they are actually performing an array calculation simply because they didn't need CSE.
I find it less well-known that a formula placed into the 'refers to' box of a Name will also be evaluated as an array every time the Name is referenced. Thus
=SUM( myArrayFormulaByName )
does not need CSE even though it would if the parameter were an explicitly written array formula.
PeterBartholomew1 - yes, agree. Named ranges/formulas in general are not very popular, even tables which in some extent could be considered as dynamic named ranges.
I would consider tables as the ideal way of holding the initial input and reference data for a model. My aim when developing a solution is to capture as much structure as I can within the solution and, also, to link it as tightly as possible to the terminology and assumptions of the application domain.
That rather contrasts with the normal 'tips and tricks' genre aimed at achieving (bad?) solutions faster. Speed is good but it should not be the main objective.