ARF

=T_A1GGH($A$11:$G$16,J10,6,,,)

3

a

t

w

x

31

d

c

2

-1

2

3

a

c

d

q

32

-1

3

-1

2

q

33

2

d

34

35

a3

Other function on minisheet

36

q

2

AFLAT

37

2

q

38

39

This proves the functionality of the kit, whatever function we can use to calculate an array and return a single cell result or 1D horiz array, can be done recursively to many arrays using the kit.

40

It will be nice to see others function creations posted here!!!

41

ARF post

Cell Formulas
Range	Formula
A8,G29,G21,G10	A8	=FORMULATEXT(A9)
A9:A11	A9	=LET(s,SEQUENCE(3),SWITCH(s,1,"a",2,"b",3,2))
G11:I13	G11	=APP((C11:D11,C14:E14,C17:D17),,)
G22:L22	G22	=TRANSPOSE(SORT(UNIQUE(AFLAT(B21:D23))))
G30:M32	G30	=ASU((B21:D23,B29:D33,B36:C37),,)
Dynamic array formulas.

Xlambda · Jul 28, 2021

Task 10: To build the equivalent of tool lambda T_AGG (Task 7) , this time for single arrays T_A1GGH (horizontal/by rows) !! recursive !! T_A1GGH(a,fn,o,k,ai,i)

Excel Formula:

=LAMBDA(a,fn,o,k,ai,i,
    LET(n,ROWS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,j,),IF(j=0,ai,
      T_A1GGH(a,fn,o,k,IF(s=j,
         IFS(AND(fn>=1,fn<=13),AGGREGATE(fn,o,x),AND(fn>=14,fn<=19),AGGREGATE(fn,o,x,k),fn=20,TRANSPOSE(MODE.MULT(x)),TRUE,"check values"),ai),j-1))
    )
)

AGG study.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

1

Task 10: To build the equivalent of tool lambda T_AGG (Task 7) , this time for single arrays T_A1GGH (horizontal/by rows)

2

Following the same reasoning as in Task 7 post, we already have identified F(x,fn,o,k)

3

F(x,fn,o,k)=IFS(AND(fn>=1,fn<=13),AGGREGATE(fn,o,x),AND(fn>=14,fn<=19),AGGREGATE(fn,o,x,k),fn=20,TRANSPOSE(MODE.MULT(x)),TRUE,"check values")

4

Using A1RFH kit.

5

A1RFH(a,ai,i)=LAMBDA(a,ai,i,LET(n,ROWS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,j,),IF(j=0,ai,A1RFH(a,IF(s=j,F(x),ai),j-1))))

6

Will get to:

7

T_A1GGH(a,fn,o,k,ai,i)=LAMBDA(a,fn,o,k,ai,i,LET(n,ROWS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,j,),IF(j=0,ai,T_A1GGH(a,fn,o,k,IF(s=j, IFS(AND(fn>=1,fn<=13),AGGREGATE(fn,o,x),AND(fn>=14,fn<=19),AGGREGATE(fn,o,x,k),fn=20,TRANSPOSE(MODE.MULT(x)),TRUE,"check values"),ai),j-1))))

8

9

o,6,k,ignored

AGGREGATE functionality: AGGREGATE( function_num, options, array, [k] )

10

sample

fn

1

2

3

4

5

6

7

8

9

10

11

12

13

20

function_num arg. "fn"

options argument "o"

11

1

2

a

4

7

4

↓

function

↓

values to be ignored

12

8

b

8

14

c

14

11

4

7

14

8

12544

3.46

3

44

12

9

11

8

14

1

Average

0

Ignore nested Subtotal & Aggregate functions

13

d

3

5

3

5

d

4

6

5

3

225

1.15

1

16

1.33

1

4

3

5

2

Count

1

Ignore hidden rows and nested Subtotal & Aggregate functions

14

6

g

8

g

8

7

4

6

8

6

2304

1.15

1

28

1.33

1

7

6

8

3

Counta

2

Ignore error values and nested Subtotal & Aggregate functions

15

9

5

9

5

7

6

9

5

91125

2.19

2

42

4.8

4

7

9

5

4

Max

3

Ignore hidden rows, error values and nested Subtotal & Aggregate functions

16

d

3

a

3

4

3.5

4

6

4

3

144

0.58

0.5

14

0.33

0.25

3.5

3

4

5

Min

4

Ignore nothing

17

=T_A1GGH($A$11:$G$16,X10,6,,,)

6

Product

5

Ignore hidden rows

18

7

Stdev.S

6

Ignore error values

19

o,6

8

Stdev.P

7

Ignore hidden rows and error values

20

k

2

0.2

2

0.2

2

9

Sum

21

fn

14

15

16

17

18

19

10

Var.S

22

11

Var.P

23

14

8

11

8

11

12

Median

The functions that require argument k

24

5

3

4

3

4

13

Mode.Sngl

function

meaning of k

25

8

6

7

6

7

14

Large

14

Large

Return the k'th largest value

26

9

5

7

5

7

15

Small

15

Small

Return the k'th smallest value

27

4

3

3.5

3

3.5

16

Percentile.Inc

16

Percentile.Inc

Return the k'th percentile

0<=k<=1

28

=T_A1GGH($A$11:$G$16,J21,6,J20,,)

17

Quartile.Inc

17

Quartile.Inc

Return the k'th quartile

0<=k<=4(<5)

29

18

Percentile.Exc

18

Percentile.Exc

Return the k'th percentile

0<k<1

30

single cell formula fn,{1,2,…,13},o,6,k,ignored

19

Quartile.Exc

19

Quartile.Exc

Return the k'th quartile

1<=k<=3(<4)

31

=T_A1GGH(A11:G16,SEQUENCE(,13),6,,,)

custom created

20

Mode.Mult

32

3.33

6

7

1

448

2.16

1.97

20

4.67

3.89

3

2

33

11

4

7

14

8

12544

3.46

3

44

12

9

11

8

34

4

6

5

3

225

1.15

1

16

1.33

1

4

3

35

7

4

6

8

6

2304

1.15

1

28

1.33

1

7

6

36

7

6

9

5

91125

2.19

2

42

4.8

4

7

9

37

3.5

4

6

4

3

144

0.58

0.5

14

0.33

0.25

3.5

3

38

39

single cell formula fn,{14,15,16,17,18,19},o,6,k,{2,2,0.2,2,0.2,2}

single cell formula for multiple k arg.

40

=T_A1GGH(A11:G16,{14,15,16,17,18,19},6,{2,2,0.2,2,0.2,2},,)

=T_A1GGH(A11:G16,14,6,{1,2,3},,)

41

4

2

3

1.4

3

=T_A1GGV($A$12:$F$18,H13,6,,,)

4

42

14

8

11

8

11

14

8

43

5

3

4

3

4

5

3

44

8

6

7

6

7

8

6

45

9

5

7

5

7

9

46

4

3

3.5

3

3.5

4

3

47

A1RF post 2

Cell Formulas
Range	Formula
J10:V10	J10	=SEQUENCE(,13)
X11:Y16,J11:V16	J11	=T_A1GGH($A$11:$G$16,J10,6,,,)
J17,J28	J17	=FORMULATEXT(J11)
U17	U17	=FORMULATEXT(X11)
J22:O27	J22	=T_A1GGH($A$11:$G$16,J21,6,J20,,)
J31,V40,J40	J31	=FORMULATEXT(J32)
J32:V37	J32	=T_A1GGH(A11:G16,SEQUENCE(,13),6,,,)
J41:O46	J41	=T_A1GGH(A11:G16,{14,15,16,17,18,19},6,{2,2,0.2,2,0.2,2},,)
V41:X46	V41	=T_A1GGH(A11:G16,14,6,{1,2,3},,)
Dynamic array formulas.

Xlambda · Jul 28, 2021

Task 11: To build the equivalent of tool lambda T_AGG (Task 7) , this time for single arrays T_A1GGV (vertical/by columns) !! recursive !! T_A1GGV(a,fn,o,k,ai,i)

Excel Formula:

=LAMBDA(a,fn,o,k,ai,i,
    LET(n,COLUMNS(a),s,SEQUENCE(,n),j,IF(i="",n,i),x,INDEX(a,,j),IF(j=0,ai,
      T_A1GGV(a,fn,o,k,IF(s=j,
        IFS(AND(fn>=1,fn<=13),AGGREGATE(fn,o,x),AND(fn>=14,fn<=19),AGGREGATE(fn,o,x,k),fn=20,MODE.MULT(x),TRUE,"check values"),
          ai),j-1))
    )
)

AGG study.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

1

Task 11: To build the equivalent of tool lambda T_AGG (Task 7) , this time for single arrays T_A1GGV (vertical/by columns)

2

Following the same reasoning as in Task 7 post, we already have identified F(x,fn,o,k)

3

F(x,fn,o,k)=IFS(AND(fn>=1,fn<=13),AGGREGATE(fn,o,x),AND(fn>=14,fn<=19),AGGREGATE(fn,o,x,k),fn=20,TRANSPOSE(MODE.MULT(x)),TRUE,"check values")

4

Taking in consideration the orientation we also have to modify TRANSPOSE(MODE.MULT(x)) to MODE.MULT(x)

5

F(x,fn,o,k)=IFS(AND(fn>=1,fn<=13),AGGREGATE(fn,o,x),AND(fn>=14,fn<=19),AGGREGATE(fn,o,x,k),fn=20,MODE.MULT(x),TRUE,"check values")

6

Using A1RFV kit:

7

A1RFV(a,ai,i)=LAMBDA(a,ai,i,LET(n,COLUMNS(a),s,SEQUENCE(,n),j,IF(i="",n,i),x,INDEX(a,,j),IF(j=0,ai,A1RFV(a,IF(s=j,F(x),ai),j-1))))

8

Will get to:

9

T_A1GGV(a,fn,o,k,ai,i)=LAMBDA(a,fn,o,k,ai,i,LET(n,COLUMNS(a),s,SEQUENCE(,n),j,IF(i="",n,i),x,INDEX(a,,j),IF(j=0,ai,T_A1GGV(a,fn,o,k,IF(s=j, IFS(AND(fn>=1,fn<=13),AGGREGATE(fn,o,x),AND(fn>=14,fn<=19),AGGREGATE(fn,o,x,k),fn=20,MODE.MULT(x),TRUE,"check values"),ai),j-1))))

10

11

sample

o,6,k,ignored

o,6

12

1

8

d

6

9

d

fn

fn

k

=T_A1GGV($A$12:$F$18,P13,6,Q13,,)

13

2

b

6

9

3

1

3.333

11

4

7

3.5

14

2

4

14

5

8

9

4

14

2

b

3

g

a

2

6

4

6

4

15

2

8

3

6

5

3

15

a

8

5

8

5

3

6

16

0.2

8

3

6

5

3

16

4

14

3

9

3

4

14

5

8

9

4

17

2

11

4

7

3.5

17

4

c

5

g

5

4

5

1

8

3

6

5

3

18

0.2

1.4

8

3

6

5

3

18

7

14

d

8

5

4

6

448

12544

225

2304

91125

144

19

2

11

4

7

3.5

19

7

2.16

3.464

1.155

2.191

0.577

20

8

1.972

3

1

2

0.5

21

9

20

44

16

28

42

14

22

10

4.667

12

1.333

4.8

0.333

23

11

3.889

9

1

4

0.25

24

12

11

4

7

3.5

25

13

=T_A1GGV($A$12:$F$18,H27,6,,,)

8

3

6

9

3

26

27

20

=T_A1GGV(A12:F18,SEQUENCE(13),6,,,)

8

3

6

9

3

28

4

14

5

8

5

4

29

30

single cell formula fn,{1;2;…;13},o,6,k,ignored

31

(fn multiple argument array should follow rows constantant array syntax (";" separator instead of ",")

32

33

3.333

11

4

7

3.5

single cell formula for multiple k arguments

34

6

4

6

4

=T_A1GGV(A12:F18,14,6,{1;2;3},,)

35

7

6

=BYROW(A4:C5,LAMBDA(a,MAX(a)))

14

5

8

9

4

36

7

14

5

8

9

4

14

5

8

9

4

37

1

8

3

6

5

3

4

8

3

6

9

3

38

448

12544

225

2304

91125

144

39

2.16

3.464

1.155

2.191

0.577

40

1.972

3

1

2

0.5

41

20

44

16

28

42

14

42

4.667

12

1.333

4.8

0.333

43

3.889

9

1

4

0.25

44

3

11

4

7

3.5

45

2

8

3

6

9

3

46

A1RF post 3

Cell Formulas
Range	Formula
I12,P34,I32,I26,R12	I12	=FORMULATEXT(I13)
R13:W18	R13	=T_A1GGV($A$12:$F$18,P13,6,Q13,,)
I27:N28,I13:N25	I13	=T_A1GGV($A$12:$F$18,H13,6,,,)
I33:N45	I33	=T_A1GGV(A12:F18,SEQUENCE(13),6,,,)
P35:U37	P35	=T_A1GGV(A12:F18,14,6,{1;2;3},,)
Dynamic array formulas.

smozgur · Jul 28, 2021

@Xlambda / @schardt679

Did you get the new functions in the latest update?

Excel First Look At Lambda Helper ByRow And By Col 2415

A first look at the new Excel functions BYROL and BYCOL. These functions, released to Insiders Fast this week are designed to be used with LAMBDA functions. Read more about the 7 functions here...

www.mrexcel.com

They are very good improvements. Although I didn't have a chance to play with them in detail, I can say that I already love MAP, BYs, and REDUCE.

Xlambda · Jul 28, 2021

smozgur said:
@Xlambda / @schardt679

Did you get the new functions in the latest update?

Excel First Look At Lambda Helper ByRow And By Col 2415

A first look at the new Excel functions BYROL and BYCOL. These functions, released to Insiders Fast this week are designed to be used with LAMBDA functions. Read more about the 7 functions here...

www.mrexcel.com

They are very good improvements. Although I didn't have a chance to play with them in detail, I can say that I already love MAP, BYs, and REDUCE.

Wow, amazing, I have them!!! Did not knew. Thanks for letting us know. ?✌✌ Functions designed with LAMBDA integration, wow, this is a huge step forward, don't you think??!! I love it.
Great statement from Microsoft, seems like LAMBDA has a great future. See ? Looks like Microsoft is reading my mind ? Exactly these type functionalities are covered in this very post, with help of recursion versatility, and also I have addressed these with lot of other functions from the beginning like AAGREGATE etc. Means we are rowing the same boat. Can wait to dive into them. Probably a lot of functions here will be rewritten. Always my goal was to write few rows , short but powerful formulas. If a formula or function is not short, means we did not get to the essence of it. Now looks that will have tools to make them even shorter. Great fun is coming. Thanks again and kudos to Microsoft!!

smozgur · Jul 28, 2021

Xlambda said:
Wow, amazing, I have them!!! Did not knew. Thanks for letting us know. ?✌✌ Functions designed with LAMBDA integration, wow, this is a huge step forward, don't you think??!! I love it.
Great statement from Microsoft, seems like LAMBDA has a great future. See ? Looks like Microsoft is reading my mind ? Exactly these type functionalities are covered in this very post, with help of recursion versatility, and also I have addressed these with lot of other functions from the beginning like AAGREGATE etc. Means we are rowing the same boat. Can wait to dive into them. Probably a lot of functions here will be rewritten. Always my goal was to write few rows , short but powerful formulas. If a formula or function is not short, means we did not get to the essence of it. Now looks that will have tools to make them even shorter. Great fun is coming. Thanks again and kudos to Microsoft!!

These are really good improvements. I can't wait to play with them.

schardt679 · Jul 28, 2021

I did get them. I'll have to play around with them. There's not a lot of info on them yet. This is huge. I think it will help make currently long LAMBDAs a lot shorter. It will be cool to mix BYCOL, BYROW, SCAN, MAP, REDUCE, MAKEARRAY, and ISOMITTED to rewrite existing LAMBDAs and incorporate them into new ones.

Xlambda · Jul 28, 2021

Very first glimpse of new function BYROW

AGG study.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

Very first glimpse on new!! BYROW function

2

like in MrExcel video

3

4

1

3

2

=BYROW(A4:C5,LAMBDA(a,AGGREGATE(14,6,a,2)))

5

4

2

5

6

works fine

7

Seems that any function that returns a single result, applied to every row of an array , will produce an array of results accordingly

8

9

=BYROW(A4:C5,LAMBDA(a,AGGREGATE(14,6,a,{1,2})))

10

4

11

second largest value of each row, also works because returns a single value

12

13

14

#CALC!

15

top 2 largest values of each rows does not work, because it does not return a single result

16

k argument of AGGREGATE {1,2} is forcing an array result, not a single result

17

{1;2} does not work either, obviously

18

19

=T_A1GGH(A4:C5,14,6,{1,2},,)

20

=BYROW(A4:C5,LAMBDA(a,SORT(a)))

2

21

5

4

22

my function, (Task 11 of this post) can handle this, means that I still have a job ?

23

24

25

#CALC!

26

SORT also does not work , array result

27

I will make one that does ?✌

28

BYCOL,BYROW

Cell Formulas
Range	Formula
E3,E24,E19,E13,E8	E3	=FORMULATEXT(E4)
E4:E5	E4	=BYROW(A4:C5,LAMBDA(a,MAX(a)))
E9:E10	E9	=BYROW(A4:C5,LAMBDA(a,AGGREGATE(14,6,a,2)))
E14	E14	=BYROW(A4:C5,LAMBDA(a,AGGREGATE(14,6,a,{1,2})))
E20:F21	E20	=T_A1GGH(A4:C5,14,6,{1,2},,)
E25	E25	=BYROW(A4:C5,LAMBDA(a,SORT(a)))
Dynamic array formulas.

schardt679 · Jul 28, 2021

Also as part of this update, it seems when working with dates and one is returned, Excel automatically updates the cell using the source date formatting

It seems it only does it for the first date in an array.

Using the MAKEARRAY function should help with the multiple answer for each row/column problem

Xlambda · Jul 28, 2021

I wish but I don't think so, since specific arguments rows and columns are required

Book2

A

B

C

D

E

F

G

1

figure it out a simple example of what MAKEARRAY does

2

=MAKEARRAY(2,3,LAMBDA(a,b,a+b))

3

=SEQUENCE(2)+SEQUENCE(,3)

3

4

3

4

5

equivalent of:

6

7