Forum Discussion
ByRow function produces two different results depending on whether row is range or array
In a nutshell, I am trying to compute average with a combination of ByRow, Index, and Average. I needed a sequence number array for this algorithm. When I put this sequence number in a range and use ...
SergeiBaklan seems to have got to the bottom of the problem. Yet another example of the failure of the Calc engine to address the problem of arrays of arrays (that is, 'normal' problems).
I do not have much extra to offer; I was interested in trying to master the new AFE. I decided that the problem is better suited to MAP, so bypassed the problem rather than addressing it. Rather than showing the anonymous Lambda function on the worksheet, I defined a Named Lambda 'AverageLast7λ' in its Curried form, giving the worksheet formula
=LET(
Values, TAKE(SORT(Data), , -1),
Sqn, SEQUENCE(ROWS(Values)),
MAP(Sqn, AverageLast7λ(Values))
);
AverageLast7λ
= LAMBDA(v, LAMBDA(a,
IF(a<7,"NA",
AVERAGE(INDEX(v, SEQUENCE(7, 1, a,-1)))
)
));The SORT and AVERAGE formulas are different, but it is simply change for change's sake.
AverageLast7λ
= LAMBDA(v, LAMBDA(a,
IF(a<7,"NA",
AVERAGE(TAKE(TAKE(v, a),-7))
)
));also worked.
- Thanks, Peter. That definitely adds a degree of elegance to the formula.
I have been relying too much on Index for looping. I indeed need to do a bit of unlearning and should start working with Map and Take.
