Forum Discussion
Solved
Excel 365 SUM dynamic array at row level
Hi (no LAMDBA here). I've been struggling with this for a moment, searched here & there for something similar to no avail. In the below pic. Dummy arr is the representation of an array that doesn't...
Perhaps for an array of max values:
=LET(
i,SEQUENCE(ROWS(array)),
m,COLUMNS(array),
arrayRank,i+PERCENTRANK.EXC(array,array,15),
rowRank,SMALL(arrayRank,i*m),
rowTot,MMULT(array*(arrayRank=rowRank),1^i),
rowFreq,MMULT(N(arrayRank=rowRank),1^i),
rowTot/rowFreq
)
Change i*m to (i-1)*m+1 for array of min values
In the given example:
array ={1,2,3;10,20,30;100,200,300}
arrayRank ={1.1,1.2,1.3;2.4,2.5,2.6;3.7,3.8,3.9}
rowRank ={1.3;2.6;3.9}
rowTot ={3;30;300}
The result is divided by the number of occurences of the max value if there is more than one.