Forum Discussion
Excel 365 SUM dynamic array at row level
Tried to repeat that from scratch, manipulating with indexes is not my strong point. Thus nothing new is here
=LET(
n, ROWS(array),
m, COLUMNS(array),
nm, n*m,
k, SEQUENCE(n),
j, SEQUENCE(nm),
sPos, XMATCH(array,SMALL(array, j )),
lPos, XMATCH(array,LARGE(array, j)),
sSec, MOD(SMALL(nm*k+sPos-1, k*m),nm)+1,
lSec, MOD(SMALL(nm*k+lPos-1, k*m),nm)+1,
sumR, MMULT(array,SEQUENCE(m,1,1,0)),
CHOOSE({1,2,3},sumR, LARGE(array,lSec), SMALL(array,sSec)))
Indeed, that formula follows similar logic. I learnt from this exercise several alternatives to RANK(array,array,-1) exist which permit arrays in arguments including,
=MATCH(array,SMALL(array,SEQUENCE(COUNT(array))))
=PERCENTRANK.EXC(array,array,15)*COUNT(array,)
I like the use of dynamic criteria here, it might even open the door to database functions like
=DSUM(Ref,j,Crit)
for an array of column sums (where Ref and Crit are dynamic references and j={1,2,...,m})
- No worries at all and even if it was for me I would have taken the point (positively)
Nice day... Lorenzo , thanks. Nope, my message was not for you, sorry if I articulate it wrongly. That was just an observation in general.
To all. I feel extremely embarrased that you spent time & efforts on the MIN/MAX as it was curiosity only on my side. And worst, I won't be able to mark more that 1 reply as best answer (expect if I missed something). In any case in big THANK YOU to all of you, I'll definitively learn from what you shared
(in a-z order) lori_m
On a couple of occasions I got a #VALUE! error with your version involving PERCENTRANK.EXC (might be me...). The one in the workbook you shared seems OK after a few checks. Like your DSUM, DMIN & DMAX approach@Peter Bartholomew
Thanks for sharing your workbook. Like the approach and will learn from it@Sergei Baklan
Re. helper in grid is very often real helper, don't know why people try to avoid it and overcomplicate formulas
In case the msg was for me 🙂 I'm not at all against helper columns/rows or hidden helper sheets, I use them quite often
Your last formula with XMATCH & MOD seems OK as well after a few checksYou guys are amazing
THANKS MUCH AGAINcouple more variations attached..
