Jun 16 2021 11:09 PM
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 exist on a sheet, it's calculated in a LET expression and in reality can involve more columns and rows
Question: is there a way to i.e. SUM that array row by row and get the below Expected arr (not on a sheet)?
Thanks
(I posted the question yesterday at a time where the site had issues. Can't find that post today...)
Jun 16 2021 11:28 PM - edited Jun 16 2021 11:35 PM
SolutionJun 17 2021 12:47 AM
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?
Jun 17 2021 05:13 AM
If for the range in grid
=SUBTOTAL({5,4},OFFSET(INDEX(A2:C4,1,1),SEQUENCE(ROWS(A2:C4),,0),,,COLUMNS(A2:C4)))
Jun 17 2021 10:29 AM
Jun 17 2021 10:55 AM
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.
Jun 18 2021 10:11 AM
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.
Jun 19 2021 02:24 AM - edited Jun 19 2021 02:43 AM
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.
Jun 19 2021 04:51 AM
Not 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))
Jun 19 2021 09:45 AM
Some neat tricks, which address one of the weaknesses of dynamic arrays which still cause developers to revert to the flawed concepts of copying formulas with relative references!
In normal use, array formulas largely replace the need for helper ranges, so reducing the risk of error. In the attached, I have taken a different strategy and allowed the array formulas to write to dynamic ranges to a hidden helper sheet so that the xxxIFS family of functions may be used.
p.s. @L z. Matrix equations are not that scary! Because of the nature of my former employment, I have probably written more matrix equations than I have English language paragraphs!
where 'mask' is defined to by
= IF(dummyArr#,k)
Jun 19 2021 09:46 AM
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))
Jun 19 2021 10:32 AM
Yes, you are right. First two (all zeroes or all same negative) could be solved if add any number to maxN, e.g. 1. But rounding and big numbers is the real issue.
Your solution is much more reliable and looks very nice, will play with it some later.
Jun 19 2021 10:41 AM
@Peter Bartholomew , helper in grid is very often real helper, don't know why people try to avoid it and overcomplicate formulas. I like your solution.
Jun 19 2021 01:24 PM
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)))
Jun 19 2021 03:39 PM
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})
Jun 19 2021 11:57 PM
couple more variations attached..
Jun 20 2021 06:03 AM
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
Your last formula with XMATCH & MOD seems OK as well after a few checks
You guys are amazing
THANKS MUCH AGAIN
Jun 21 2021 12:13 PM
@L z. , thanks. Nope, my message was not for you, sorry if I articulate it wrongly. That was just an observation in general.
Jun 21 2021 09:26 PM