Transform classic formula to a dynamic array formula

Question

I'm trying to transform my clasic formula to a dynamic array one, but i can't

My current working classic formula is

=LET(
per; B4;    
data; FILTER(CHOOSE({1\2}; Taula1[Cap];Taula1[Cap]*Taula1[Tipo]); Taula1[Per]=per);    
res; SUM(INDEX(data;;2)) / SUM(INDEX(data;;1) );
res)

But it doesn't run when i'm trying to transform the formula changing the parameter to a dynamic array B4#

Now I copy/paste the classic formula to next rows

The result must be:

And source data was:

Not sure may be something like this would work: =LET( per, B4#, data, FILTER(CHOOSE({1,2,3}, Taula1[Per],Taula1[Cap],Taula1[Cap]*Taula1[Tipo]), ISNUMBER(XMATCH(Taula1[Per],per))), uniq,UNIQUE(INDEX(data,,1)), rowwise,BYROW(uniq,LAMBDA(x,SUM(--(x=INDEX(data,,1))*(INDEX(data,,3)))/SUM(--(x=INDEX(data,,1))*(INDEX(data,,2))))), HSTACK(uniq,rowwise)) — Mayukh Bhattacharya
– Mayukh Bhattacharya, Commented Aug 27, 2023 at 13:39

lluis · Accepted Answer · 2023-08-27 16:49:51Z

1

Excelent !!!

=LET(per; B4#;
     rowwise; BYROW(per; LAMBDA(x; SUM(--(x=Taula1[Per])*(Taula1[Cap])*(Taula1[Tipo]) ) / SUM(--(x=Taula1[Per])*(Taula1[Cap])) ));
     res; HSTACK(per; rowwise);
     res)

I didn't have enough experience with BYROW function

Thanks a lot

answered Aug 27, 2023 at 16:49

lluis

856 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mayukh Bhattacharya Over a year ago

This really nice, i was thinking about making it short, but due to very less information i was not able to think more, also you had not responded in the mean time, ok, since you have resolved it ensure to accept your own answer so it helps someone in the future.

Collectives™ on Stack Overflow

Transform classic formula to a dynamic array formula

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related