Forum Discussion
ByRow function produces two different results depending on whether row is range or array
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.
PeterBartholomew1 , hope you are happy with new AFE. Had no time to dig it in details, first impression is quite positive.
- As for you, it is early days. I do not think I have fully mastered the options available on the Names tab. I have been able to do a bit of editing though.
Modules, I am pretty comfortable with.
Grid is amazing. Hopefully, I will soon be able to switch the formula bar off forever!I have a similar question. Suppose I have an array (A1#) such that:
2 3 4 I need to create a sequence that in the end looks something like this:
2 2 3 3 3 4 4 4 4 My thought was the use BYROW(A1#,LAMBDA(x,SEQUENCE(1,4,x,0))) so that I get:
2 2 N/A N/A 3 3 3 N/A 4 4 4 4 And then feed that into TOCOL() and use a FILTER() to filter out the "N/A"s. But the function throws a CALC error.
Any thoughts?
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.
