Forum Discussion
Excel 365 SUM dynamic array at row level
Thanks much. Classic, classic, for you :))
I'm more used to do a SIGN(SEQUENCE(COLUMNS(A2:C4))) but got it now and did what I actually needed to do as well (Average)
However, I don't see how a MIN or MAX would be doable. Surely you have another classic :)) trick for this, don't you?
If for the range in grid
=SUBTOTAL({5,4},OFFSET(INDEX(A2:C4,1,1),SEQUENCE(ROWS(A2:C4),,0),,,COLUMNS(A2:C4)))Another classic. SUBTOTAL(OFFSET()).
I always struggle with this one because formula evaluation returns #VALUE! at a certain point. Nevertheless the final result is correct.
- Nice one Sergei. Thank you too. I'll keep this thread (oups, Discussion) open for a few days in case someone would have a bright idea to make it work off the grid
Thanks againNot sure that always works correctly
=LET( maxN, MAX(array)+SIGN(MIN(array))*MIN(array), m, COLUMNS(array), r, ROWS(array), i, SEQUENCE(r), arrayM, i+array/maxN, sumR, MMULT(array,SEQUENCE(m,1,1,0)), largeR, LARGE(arrayM,(SEQUENCE(r,,r,-1)-1)*m+1) - i, smallR, SMALL(arrayM,(i-1)*m+1) - i, CHOOSE({1,2,3}, sumR, smallR*maxN, largeR*maxN))That looks ok for 'normal' values, that is
- at least one positive value in grid (eg not all zero or all -1)
- values do not differ by too many orders of magnitude (eg array doesn't contain both 1E+20 and 1)Another option that might avoid division by zero and floating point inaccuracies,
=LET( i,SEQUENCE(ROWS(array)), m,COLUMNS(array), n,COUNT(array), rank,MATCH(array,SMALL(array,SEQUENCE(n))), rowRank,MOD(SMALL(n*i+rank-1,i*m),n)+1, SMALL(array,rowRank))
Sum which Detlef_Lewin suggested shall work out of grid, like
=MMULT(array,SEQUENCE(COLUMNS(array),1,1,0))Min and Max based on OFFSET definitely won't work, afraid we need lambdas here. That's interesting task, will try to play with it.
