Forum Discussion
How to make changes ranges in an array/formula without manually modifying it each time
That's awesome, and interesting.
I've always hated array formula's because of the whole Ctrl+Enter thing. If this works you could technically build a whole formula in cell A1 (minus the curly brackets{}). and then make your array formula only the indirect i.e. ={INDIRECT(A1)} if that maintains the Ctrl enter, you could make changes without the worry of accidentally not hitting Ctrl+Enter.
Although that only works if you only have the formula in 1 cell and aren't filling down the formula.
Anyway glad your workbook is working.
Don't let the inadequacies of the user interface put you off the clarity of thought that comes with array formulas. Virtually no one uses array formulas so it is not surprising that the user interface is clunky and inconvenient (I once suggested that the defaults should be reversed and those who wish to use implicit intersection to destroy arrays should be forced to go through the inconvenience of CSE!)
For this question, I had waded through the sea of direct notation, if finished with
{= IFERROR( INDEX( ValueArray, SMALL( IF( TestArray=Criterion, k ), k ) ), "")}
How one defines ValueArray is a separate issue; it would normally be absolute but could be defined as column relative or movable through the use of OFFSET.
What is now possible with dynamic arrays is to reduce the whole formula to
= FILTER( ValueArray, TestArray=Criterion, "Null" )
My other observation would be that a formula defined as Named formula always has been protected against the ravages of implicit intersection. That provides an alternative strategy to hiding array calculations in one of the few functions that work properly with arrays (AGGREGATE, SUMPRODUCT, LOOKUP etc.)
PeterBartholomew1 , you may wrap array calculations by AGGREGATE, SUMPRODUCT,... without named ranges, isn't it?
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.
ILikePi ,
I your case you may use AGGREGATE to avoid array formula. And, as variant, if your ranges are always start from the same row (second one) and they are of same height, OFFSET could be used for the reference. If number of columns to offset from column A are parametrised in cells A1 and B1 that could be like
=IFERROR(INDEX(OFFSET($A$1,1,$B$1,250),AGGREGATE(15,6,1/($U$1=OFFSET($A$1,1,$A$1,250))*(ROW($C$2:$C$25)-1),(ROW()-1))),"")
