ASCAN

3

6

10

3

6

10

2

3

4

5

6

7

8

15

21

28

36

5

11

18

26

6

8

10

12

5

9

10

11

12

45

55

66

78

9

19

30

42

15

18

21

24

6

13

14

15

16

91

105

120

136

13

27

42

58

28

32

36

40

7

8

d,omitted (by array)

d,-1 (by rows)

d,1 (by clms)

9

sample 2

a

10

2

3

4

3

6

10

3

6

10

2

3

4

11

5

6

7

8

15

21

28

36

5

11

18

26

6

8

10

12

9

10

11

12

45

55

66

78

9

19

30

42

15

18

21

24

13

14

15

16

91

105

120

136

13

27

42

58

28

32

36

40

14

17

18

19

20

153

171

190

210

17

35

54

74

45

50

55

60

15

21

22

23

24

231

253

276

300

21

43

66

90

66

72

78

84

16

17

Note: Did not set an extra argument for any kind of "initial value", since the only thing it does is too simple,

18

adds a constant value to the final outcome , like in 10+ASCAN(a)

19

ASCAN post

Cell Formulas
Range	Formula
F2,P9,K9,F9,P2,K2	F2	=FORMULATEXT(F3)
F3:I6	F3	=ASCAN(A3:D6)
K3:N6	K3	=ASCAN(A3:D6,-1)
P3:S6	P3	=ASCAN(A3:D6,1)
A10:D15	A10	=SEQUENCE(6,4)
F10:I15	F10	=ASCAN(A10#)
K10:N15	K10	=ASCAN(A10#,-1)
P10:S15	P10	=ASCAN(A10#,1)
Dynamic array formulas.

Xlambda · Oct 31, 2023

You are very good. If you made this using a mobile, you are a genius.

To upload something use a cloud downloadable link.
I got it, you need kind of countif byrow then a min btw 2 arrays and a sum byrow. No problem. We do not need countif at all. Or concat and mid.
Here is a simple function concept a countif by criteria function, (ar,array; cr,criteria)
CIF(ar,cr)

Excel Formula:

=LAMBDA(ar, cr, DROP(REDUCE(0, cr, LAMBDA(v, i, HSTACK(v, BYROW(N(ar = i), LAMBDA(x, SUM(x)))))), , 1))

And the formula that does all:
=LET(s,{0,1,2,3},BYROW(MAP(CIF(A1#,s),CIF(F1#,s),LAMBDA(x,y,MIN(x,y))),LAMBDA(x,SUM(x))))
Hope you'll like it.

Note: For @smozgur . The latest update is amazing. Great job !!!!

Book1.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

AK

AL

AM

AN

AO

1

1

0

0

3

1

=MMULT(MAP(MID(BYROW(A1:D23,LAMBDA(x,CONCAT(COUNTIF(x,SEQUENCE(,4,0,))))),{1,2,3,4},1)*1,MID(BYROW(F1:I23,LAMBDA(y,CONCAT(COUNTIF(y,SEQUENCE(,4,0,))))),{1,2,3,4},1)*1,LAMBDA(x,y,MIN(x,y))),{1;1;1;1})

2

3

0

1

2

0

1

3

0

2

1

3

2

1

4

0

1

0

1

2

1

3

1

5

3

2

0

3

1

3

1

6

0

1

3

0

2

0

2

7

0

1

2

1

2

1

2

8

1

2

3

0

1

9

0

2

0

2

1

3

1

check results both methods

10

0

3

2

1

0

1

=AND(K1#=N1#)

11

1

2

0

3

1

0

3

1

3

TRUE

12

3

1

2

1

2

3

13

0

3

2

0

1

2

3

14

2

0

3

2

3

15

2

1

0

3

2

1

2

16

0

3

2

3

0

3

0

3

17

1

2

1

0

3

1

2

3

2

18

1

0

1

3

1

0

3

19

3

1

3

1

0

1

0

1

20

3

2

0

2

3

1

2

21

1

3

2

0

1

22

2

3

2

0

2

3

1

2

23

3

2

1

0

1

2

3

4

24

=RANDARRAY(23,4,0,3,1)

K1: your formula

25

26

27

N1:

28

=LET(s,{0,1,2,3},BYROW(MAP(CIF(A1#,s),CIF(F1#,s),LAMBDA(x,y,MIN(x,y))),LAMBDA(x,SUM(x))))

29

All steps covered by the single cell formula

30

31

This is what CIF delivers ( seq(,4,0) <=> {0,1,2,3} )

=MAP(A33#,F33#,LAMBDA(x,y,MIN(x,y)))

32

=CIF(A1#,{0,1,2,3})

=CIF(F1#,SEQUENCE(,4,0))

=BYROW(K33#,LAMBDA(x,SUM(x)))

33

0

2

0

1

2

0

34

1

2

1

0

1

0

3

35

1

2

0

1

3

0

1

0

1

36

3

1

0

2

1

0

1

0

1

37

1

0

1

2

0

3

0

1

0

1

38

3

1

0

2

0

1

2

0

2

39

1

2

0

3

1

0

1

0

2

40

0

1

2

1

2

0

2

0

1

41

2

0

2

0

1

2

0

1

0

1

42

3

0

1

2

0

1

0

1

43

1

2

0

1

0

1

3

44

0

2

1

0

1

2

1

0

1

3

45

2

0

1

2

1

0

2

0

1

0

3

46

2

0

2

0

2

0

1

2

0

1

0

3

47

2

1

0

1

2

1

0

1

0

2

48

2

0

1

2

0

2

0

1

3

49

1

2

1

0

1

2

0

1

0

2

50

1

3

0

1

2

0

1

2

0

3

51

0

2

0

2

3

1

0

1

0

1

52

1

0

1

2

0

1

2

1

0

1

2

53

0

2

0

2

1

2

0

1

0

1

54

1

0

2

1

0

1

2

0

1

2

55

1

4

56

Sheet2

Cell Formulas
Range	Formula
A1:D23,F1:I23	A1	=RANDARRAY(23,4,0,3,1)
K1:K23	K1	=MMULT(MAP(MID(BYROW(A1:D23,LAMBDA(x,CONCAT(COUNTIF(x,SEQUENCE(,4,0,))))),{1,2,3,4},1)1,MID(BYROW(F1:I23,LAMBDA(y,CONCAT(COUNTIF(y,SEQUENCE(,4,0,))))),{1,2,3,4},1)1,LAMBDA(x,y,MIN(x,y))),{1;1;1;1})
N1:N23	N1	=LET(s,{0,1,2,3},BYROW(MAP(CIF(A1#,s),CIF(F1#,s),LAMBDA(x,y,MIN(x,y))),LAMBDA(x,SUM(x))))
Q10,P32,F32,A32	Q10	=FORMULATEXT(Q11)
Q11	Q11	=AND(K1#=N1#)
A24,F24	A24	=FORMULATEXT(A1)
K25	K25	=FORMULATEXT(K1)
K28	K28	=FORMULATEXT(N1)
K31	K31	=FORMULATEXT(K33)
A33:D55	A33	=CIF(A1#,{0,1,2,3})
F33:I55	F33	=CIF(F1#,SEQUENCE(,4,0))
K33:N55	K33	=MAP(A33#,F33#,LAMBDA(x,y,MIN(x,y)))
P33:P55	P33	=BYROW(K33#,LAMBDA(x,SUM(x)))
Dynamic array formulas.

Lambda Functions
Name	Formula
CIF	=LAMBDA(ar,cr,DROP(REDUCE(0,cr,LAMBDA(v,i,HSTACK(v,BYROW(N(ar=i),LAMBDA(x,SUM(x)))))),,1))

bines53 · Oct 31, 2023

Hi Xlambda,

The function will wait for me to return to the computer.
I discovered something in a certain function that probably no one noticed, very significant, and I want to investigate more, in a few days, and you will be the first to update, I promise.

Thanks,
David

smozgur · Oct 31, 2023

Xlambda said:
Note: For @smozgur . The latest update is amazing. Great job !!!!

@Xlambda: Glad to hear it helps. Thanks for the feedback!
By the way, if you enter the lambda function with line breaks, then it is displayed better in the meta section.

Cell Formulas
Range	Formula
B2	B2	=LAMBDA(a,[d], LET(y,IF(d=1,TRANSPOSE(a),a),s,SCAN(0,y,LAMBDA(v,a,v+a)),r,ROWS(s),c,COLUMNS(s),sr,SEQUENCE(r)-1, x,s-IF(d,IFERROR(INDEX(INDEX(s,,c),sr)*sr^0,0),0), IF(d=1,TRANSPOSE(x),x) ) )

bines53 · Nov 2, 2023

Hi Xlambda,

Regarding the first formula,He is not right .in total, in sum, it is true, the idea in the formula that it returns, that there is a match in location=SEQUENCE(,4,0,1)=criteria, and sum.
And the two criteria in the countifs function are not always equal.

Thanks,
David

Xlambda · Nov 3, 2023

Hi David,

I'm lost. For the context you provided and what you requested:

bines53 said:
I will give two examples about the same two ranges of data, in the ranges there are numbers between 0 and 3 .A1:D23 ,and F1:I23.The ranges has similar values.

first function,
=COUNTIFS(A1:D1,SEQUENCE(,4,0,1),F1:I1,SEQUENCE(,4,0,1)),
I want to sum each line, in one formula.

you only need this:
=BYROW(N(A1#=F1#),LAMBDA(x,SUM(x)))
For the Sum is no need of any countifs or third vector reference seq(,4,0) because the outcome is very simple. The formula calculates the sum of matching values positions by row for 2 arrays same dimensions, whatever the values are.

For other infinite tasks that I cannot guess them all, other tasks that require useful use of explicit COUNTIFS replacement methods, I have already provided the CIF(ar,cr) function that can be adapted to any need, like second task you requested.
There are also tons of ways to make a formula like byrow to spill results, even using COUNTIFS whenever specific tasks allow us.
So, if you need the results that

=COUNTIFS(A1:D1,SEQUENCE(,4,0,1),F1:I1,SEQUENCE(,4,0,1))

returns, before the sum, is very easy using CIF
=CIF(IF(A1#=F1#,A1#),SEQUENCE(,4,0))
(all single cell formulas of course)

PS: You should consider using the amazing add-in interface to post spreadsheets XL2BB - Excel Range to BBCode
and also consider asking questions here: there are so many beautiful Excel minds that can solve tasks faster and better than me.

Excel Questions

All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. Do not post questions about other spreadsheet programs (e.g. Google Sheets) in this forum.

www.mrexcel.com

On this minisheet, using random distributions, you can test indefinitely as long as you want, the acuracy of formulas you wanted by hitting F9. Same for second task already posted. ✌

Book1.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

1

2

1

3

1

2

1

0

1

0

2

0

1

2

1

3

2

1

1

0

0

1

0

3

0

1

3

1

3

1

2

4

1

2

3

0

2

3

1

1

0

0

1

0

5

0

3

1

6

0

1

0

1

0

0

1

0

7

2

1

3

2

0

8

1

0

3

2

0

3

1

0

1

9

3

2

0

3

0

3

0

1

1

0

1

10

1

3

1

0

1

2

11

1

0

1

3

0

0

0

12

2

3

2

3

2

0

2

0

2

0

0

2

0

13

2

0

3

2

0

2

0

2

0

1

0

2

0

14

0

2

3

2

1

0

0

1

0

15

3

1

2

1

0

1

0

0

1

0

16

0

3

1

3

2

3

2

1

0

1

17

0

2

3

0

2

0

1

0

1

0

1

0

18

0

2

3

0

2

3

1

0

1

0

1

0

1

0

19

2

1

3

1

2

1

3

1

0

0

1

0

20

2

0

2

1

2

0

1

3

0

1

0

1

0

1

0

21

1

0

1

3

2

22

0

2

0

1

2

1

0

1

0

0

1

0

23

3

2

3

1

3

2

=BYROW(N(A1#=F1#),LAMBDA(x,SUM(x)))

24

=RANDARRAY(23,4,0,3,1)

↑↑

25

↑↑

=SUM(K1:N1)

26

↑↑

drag and drop

single form.

↑↑

27

↑↑

check sum

↑↑

28

↑↑

=AND(P1:P23=S1#)

↑↑

29

↑↑

TRUE

↑↑

30

↑↑

31

=COUNTIFS(A1:D1,SEQUENCE(,4,0),F1:I1,SEQUENCE(,4,0))

=CIF(IF(A1#=F1#,A1#),SEQUENCE(,4,0))

32

drag and drop

single form.

33

check COUNTIFS

34

=AND(V1#=K1:N23)

35

TRUE

36

Sheet3

Cell Formulas
Range	Formula
A1:D23,F1:I23	A1	=RANDARRAY(23,4,0,3,1)
S1:S23	S1	=BYROW(N(A1#=F1#),LAMBDA(x,SUM(x)))
V1:Y23	V1	=CIF(IF(A1#=F1#,A1#),SEQUENCE(,4,0))
K1:N23	K1	=COUNTIFS(A1:D1,SEQUENCE(,4,0),F1:I1,SEQUENCE(,4,0))
P1:P23	P1	=SUM(K1:N1)
A24,F24	A24	=FORMULATEXT(A1)
P25,S25	P25	=FORMULATEXT(P1)
S28,V34	S28	=FORMULATEXT(S29)
S29	S29	=AND(P1:P23=S1#)
K31,V31	K31	=FORMULATEXT(K1)
V35	V35	=AND(V1#=K1:N23)
Dynamic array formulas.

bines53 · Nov 5, 2023

Hi Xlambda,

Here is what I promised,
The take function, together with the countif function, produces accumulation, take also works with sum ifs,I think also average ifs, I will give several examples:

1.=COUNTIF(TAKE(A1:D23,,{1,2,3,4}),SEQUENCE(4,,0))
2.=COUNTIF(TAKE(A1:D23,SEQUENCE(23,,1),4),SEQUENCE(,4,0))

3.Similar, but in reverse order ,
=COUNTIF(TAKE(A1:D23,SEQUENCE(23,,-23),4),SEQUENCE(,4,0))

4.countifs,=LET(x,SEQUENCE(23,,1),y,SEQUENCE(,4,0),COUNTIFS(TAKE(A1:D23,x,4),y,TAKE(F1:I23,x,4),y))

5.with map,
=LET(a,SEQUENCE(23,,1),b,SEQUENCE(,4,0),MAP(COUNTIF(TAKE(A1:D23,a,4),b),COUNTIF(TAKE(F1:I23,a,4),b),LAMBDA(x,y,MIN(x,y))))

Thanks,
David

Xlambda · Nov 5, 2023

Hi David,

I've called this before a static illusion of an expandable range that has no real iterative DNA versatility. (try to add a simple 0 ( take(...)+0 ) or any regular function and colapses)
Can never replace, for example a custom made bycol/byrow that spills, or scan byrow or any other native or custom-made lambda helper function with real iterative versatility that can do any function.
The fact that it works with ...IFS, but only cumulative, not simple byrow or bycol, makes them useful only when you will ever need these very specific scenarios. ✌
My goal was always to find ways to replace the range able only ...IFS functions.

. This is what happens when we play with excel, we find things that work and try to invent a use for it.
This will lead to great discoveries.

In a loop for i=1 to n take is cool because it can replace index(ar,sequence(i)) with take(ar,i), a simple way for dynamic expandable range in a loop.
PS: Still no minisheet add in?

Xlambda · Jan 15, 2024

Latest challenge of @MrExcel, a super cool algorithm challenge in general.

File link in the description or here
The first draft solution I posted it at the comments section, this is second one. There are also other ways to approach this, I will cover here only the simplest ones, shortest route, and optimized clusters.
If there is any interest or questions, I will be happy to cover stuff about the function's concepts and tricks used.
From the beginning I have simplified the initial data and replaced the long names with index numbers "k" => first clm is a sequence "s" =seq(146)
Note: Define all the names and functions for everything to work properly (they call each other). For simplicity I made it as modular as possible.
The concept is simple. For a starting point or a reference index k we calculate the shortest path to cover all elements route, 2 ways. Finally, we iterate these methods for each element to find which starting point produces the shortest path.
Defined names: pl: range ; ll: formula
pl defined name: (places) refers to the range of the orig data sorted in alphabetic order

Excel Formula:

=Sheet1!$B$2:$D$147

ll: defined name: (lat and long) replaces names with seq, defined name with a formula:

Excel Formula:

=HSTACK(SEQUENCE(ROWS(pl)),TAKE(pl,,-2))

Tool functions: (k or K refers to a single index number, s or S refers to a sequence of numbers
FL(k,s) Filters out an index nr. k (or entire row that has first clm an index nr.) from a sequence s (or an array that has first clm a seq)

Excel Formula:

=LAMBDA(k,s,FILTER(s,ISNA(XMATCH(TAKE(s,,1),TAKE(k,,1)))))

DK(k,s,[min]) Distance calculator btw an index nr k and a seq of numbers s . If min=1 => the minimum distance and its index nr. is extracted

Excel Formula:

=LAMBDA(k, s, [min],
    LET(
        x, TAKE(k, , 1),
        y, TAKE(s, , 1),
        f, FL(x, y),
        a, INDEX(ll, x, 2),
        b, INDEX(ll, f, 2),
        c, INDEX(ll, x, 3),
        d, INDEX(ll, f, 3),
        e, ACOS(SIN(RADIANS(a)) * SIN(RADIANS(b)) + COS(RADIANS(a)) * COS(RADIANS(b)) * COS(RADIANS(d) - RADIANS(c))) * 3958,
        IF(min, TAKE(SORT(HSTACK(f, e), 2), 1), e)
    )
)

DS(s,[sum]) Distance between consecutive elements of a sequence s . If sum=1 sums all the distances

Excel Formula:

=LAMBDA(s,[sum],LET(m,MAP(DROP(s,-1),DROP(s,1),LAMBDA(x,y,DK(x,y))),u,VSTACK(0,m),IF(sum, SUM(u),u)))

Main functions:
MK( k ,[ s ],[ lst ] ) Minimum consecutive distances between an index k as reference starting point and elements of a seq s, If s omitted, entire seq is used (146 places) . If lst =1 the summary of starting point k and total route distance is returned. It's an iterative function. Recursion can be used also.

Excel Formula:

=LAMBDA(k, [s], [lst],
    LET(
        a, FL(k, IF(ISOMITTED(s), SEQUENCE(ROWS(pl)), s)),
        r, REDUCE(HSTACK(k, 0), SEQUENCE(ROWS(a)), LAMBDA(v, i, VSTACK(v, DK(TAKE(v, -1), FL(v, a), 1)))),
        IF(lst, r, HSTACK(k, SUM(TAKE(r, , -1))))
    )
)

CK(k,cl,[lst]) Cluster optimization for a starting point k, a cluster radius cl. If MK can take a sample s of entire seq, CK by default uses entire "population"
If lst (list arg) =1 entire new optimized route is listed, very probably with a new starting point.
if lst is omitted, only initial k and total of the new optimized route is returned.
Note: CK calls a function that does the cluster condition distribution extraction CSCAN Consecutive SCAN. I use this custom-made lambda helper function when I need to extract the terms thar are in a certain consecutive relationship.
CSCAN alone, to be presented properly with examples of its versatility needs at least 15 posts, so, some other time.

CK(k , cl ,[ lst ])

Excel Formula:

=LAMBDA(k, cl, [lst],
    LET(
        m, MK(k, , 1),
        c, CSCAN(TAKE(m, , -1), LAMBDA(x, y, AND(x < cl, y < cl)), TAKE(m, , 1)),
        f, TAKE(c, , 1),
        x, MC(f),
        y, MK(INDEX(x, 1, 1), f, 1),
        z, XMATCH(TAKE(y, , 1), f),
        a, INDEX(c, z, SEQUENCE(, COLUMNS(c))),
        b, IF(a = "", NA(), a),
        t, TOCOL(b, 2),
        d, DS(t),
        u, SUM(d),
        IF(lst, VSTACK(HSTACK(k, u), HSTACK(t, d)), HSTACK(k, u))
    )
)

CK calls this: CSCAN(v,fn,[a],[cnt],[flt])

Excel Formula:

=LAMBDA(v, fn, [a], [cnt], [flt],
    LET(
        f, LAMBDA(k, i, IF(i = 1, 1, IF(fn(INDEX(v, i), INDEX(v, i - 1)), k, k + 1))),
        s, SCAN(0, SEQUENCE(ROWS(v)), f),
        e, DROP(FREQUENCY(s, UNIQUE(s)), -1),
        q, e >= SEQUENCE(, MAX(e)),
        c, SCAN(0, --q, SUM),
        x, INDEX(IF(ISOMITTED(a), v, a), c),
        y, IF(q, x, ""),
        w, SWITCH(cnt, 0, y, 1, HSTACK(e, y), 2, e),
        IF(flt, FILTER(w, e > 1), w)
    )
)

Now, previous functions MK(minimum or shortest distances) and CK (cluster optimized shortest distances) needed a reference index nr. k , as starting point.
We need a function to iterate the calculations for all indexes s and summarize the results in order to be able to pick the best one.
MC([ s ],[ mc ],[ cl ]) Minimum or Cluster method results for entire "population" of references s (our references represent all places in our example, more elements can be extracted or added, and everything will be dynamically calculated)
s: if omitted =seq(rows(pl)). if not omitted has relevance only for MK calc (when mc arg. is omitted) Note: s is ignored if mc=1 , when CK is triggered
mc: if omitted MK iterates for all k in s ; if not omitted CK iterates for all k in entire s
cl: cluster radius, only if mc=1, otherwise it has no effect.
MC( [ s ],[mc],[cl])

Excel Formula:

=LAMBDA([s], [mc], [cl],
    LET(
        a, IF(ISOMITTED(s), SEQUENCE(ROWS(pl)), s),
        m, MAP(a, LAMBDA(x, TEXTJOIN(",", , IF(mc, CK(x, cl), MK(x, a))))),
        SORT(--HSTACK(TEXTBEFORE(m, ","), TEXTAFTER(m, ",")), 2)
    )
)

Book2

A

B

C

D

E

F

G

H

I

J

K

1

pl (names ascending order)

=ll

2

Abajo Peak, Utah

37.8809

-109.383

37.8809

-109.383

=ROWS(F2#)

3

Ahu Tongariki, Rapa Nui (Easter Island)

-27.1126

-109.168

2

-27.1126

-109.168

146

4

Aiguestortes National Park, Spain

42.6

0.99

3

42.6

0.99

5

Alblittkopf Mountain, Austria

47.08464

10.33855

4

47.08464

10.33855

6

Ameenpur, Telangana, India

17.5

78.3196

5

17.5

78.3196

7

Andenes, Norway

69.31923

16.11369

6

69.31923

16.11369

8

Aoraki / Mount Cook, Southern Alps, New Zealand

-43.7344

170.15

7

-43.7344

170.15

9

Atacama Desert, Chile

-24

-70

8

-24

-70

10

Auroa Point, New Zealand

-39.0715

177.9018

9

-39.0715

177.9018

11

Badwater Basin, Death Valley, California

36.239

-116.841

10

36.239

-116.841

12

Baharyia Oasis Salt Lake, Eqypt

28.352

28.932

11

28.352

28.932

13

Bell Park Pier, South Africa

-28.9588

29.43389

12

-28.9588

29.43389

14

Blue Lagoon, Iceland

63.8806

-22.017

13

63.8806

-22.017

15

Cala Tuent Mallorca, Spain

39.85

2.786

14

39.85

2.786

16

Cape Evans, Ross Island, Antarctica

-76.7829

168.515

15

-76.7829

168.515

17

Cape of Good Hope, South Africa

-34

18.4174

16

-34

18.4174

18

Castle Hill, New Zealand

-43.2516

172.7613

17

-43.2516

172.7613

19

Cathedral Gorge, Australia

-17.4805

128.3727

18

-17.4805

128.3727

20

Cayo Muerto, Venezuela

10.92978

-68.2605

19

10.92978

-68.2605

Sheet1

Cell Formulas
Range	Formula
F1,J2	F1	=FORMULATEXT(F2)
F2:H147	F2	=ll
J3	J3	=ROWS(F2#)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
pl	=Sheet1!$B$2:$D$147	F2

Book2

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

Tool functions

2

FL(k,s) Filter function

DK(k,s,[min]) Dist btw k and each elem of s

DS(s,[sum]) Distances between consecutive terms of a seq s

3

4

k

4

=FL(B4,B6:B9)

k

11

min,omitted

sum,omitted

5

6

=DK(H4,H6:H10)

=DS(N6:N10)

6

s

6

5

s

32

1261.935

s

32

7

5

2

45

3227.425

45

4457.3141

8

4

120

448.4396

120

3675.7609

9

2

141

33.05862

141

474.40684

10

77

9928.763

77

9961.3049

11

12

Versatility FL

reasoning

13

k can be 2D array

1st term - dist btw 11 and 32

1st term - dist btw 32 and 32

14

s can be 2D array

2nd term - dist btw 11 and 45

2nd term - dist btw 45 and 32

15

Result keeps "s" structure

........

16

17

k

7

a

=FL(B17:C19,B21:C27)

min,1

sum,1

check

18

2

b

1234.1

19

5

234

6

34.43

141

33.05862

18568.787

18568.79

20

12

56.89

21

s

9

1234.14

35

345

!!! Cool statistical data !!!!

22

6

34.43

20

120.54

Sum of Consecutive distances off all places in alphab order

23

5

876.345

24

12

56.89

=DS(SEQUENCE(146),1)

25

35

345

717697.18

km

26

7

2345

27

20

120.54

Total route if we keep alphab. order=717,698 km

28

Sheet2

Cell Formulas
Range	Formula
D4,P24,P18,R18,J18,E17,P5,J5	D4	=FORMULATEXT(D5)
D5:D7	D5	=FL(B4,B6:B9)
J6:J10	J6	=DK(H4,H6:H10)
P6:P10	P6	=DS(N6:N10)
E18:F22	E18	=FL(B17:C19,B21:C27)
J19:K19	J19	=DK(H4,H6:H10,1)
P19	P19	=DS(N6:N10,1)
R19	R19	=SUM(P6#)
P25	P25	=DS(SEQUENCE(146),1)
Dynamic array formulas.

Book2

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

2

Main functions:

3

MK(k,[ s ],[lst]) ; MC([ s ],[mc],[cl]) ( for minimum method, to trigger only MK iterations mc,cl arguments are omitted)

4

5

Task 1: If we start from k=100

Task 2: All routes sizes for all starting points

6

? Shortest route to cover also these "places"

7

s

27

lst,1

shortest route if we start from 27

8

128

=MK(100,B7:B15,1)

=MC(B7:B15)

=MK(27,B7:B15,1)

9

57

0

27

22330.68

=>

27

0

10

21

128

568.1451

98

22689.14

100

2021.723

11

43

1144.191

21

24755.75

128

568.1451

12

98

27

2445.397

128

24842.48

43

1144.191

13

140

21

6995.732

7

24958.03

21

4723.72

14

7

57

5948

43

24961.15

57

5948

15

100

7

6240.139

100

25026.37

7

6240.139

16

140

259.8138

140

25131.23

140

259.8138

17

98

1424.951

57

25699.75

98

1424.951

18

19

Route starting from k=100 covering all "s" has a total of:

=SUM(L9:L17)

20

lst,omitt.

22330.68

21

=MK(100,B7:B14)

22

25026.37

23

24

In the ranking of routes, starting from 100 is the 7th

25

Sheet4

Cell Formulas
Range	Formula
H8,D21,L19,D8,K8	H8	=FORMULATEXT(H9)
D9:E17	D9	=MK(100,B7:B15,1)
H9:I17	H9	=MC(B7:B15)
K9:L17	K9	=MK(27,B7:B15,1)
L20	L20	=SUM(L9:L17)
D22:E22	D22	=MK(100,B7:B14)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
pl	=Sheet1!$B$2:$D$147	D9, D22, H9, K9

Xlambda · Jan 15, 2024

Book2.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

1

2

Main functions:

3

CK(k,cl,[lst]);

4

CK concept: with the help of CSCAN (Consecutive Scan), the function takes MK(k,,1) distrib and groups it for clusters < cl km radius.

5

The "tangent" values of these clusters are iterated with MC to find shortest route btw clusters

6

Last step is to add the inner cluster values to the sequence of tangent sequence.

7

8

CK method, same starting k and different cluster sizes cl.(we can write functions to iterate by cluster size, if we want)

9

cl,300

cl,500

cl,700

route distrib with MK

total route

Data interpretation

10

=CK(100,300,1)

=CK(100,500,1)

=CK(100,700,1)

MK method for k=100

no cluster method

11

87615.15

87448.66

90724.42

the reference k starting point

12

66

0

99

0

29

0

0

92743.571

total of the new route, with clusters rearranged and

13

19

1979.668

142

543.6626

121

588.4977

3

115.9907

with a new starting point, for different cluster values

14

49

915.3654

30

508.5571

117

292.1041

55

50.40737

cluster method summary

15

96

289.0288

20

977.9272

144

149.3997

14

254.6058

Note: Same starting point k and same cluster size cl, will always produce same result

16

20

741.7176

88

339.8356

42

357.6361

56

401.3614

17

88

339.8356

49

704.4787

11

1538.476

22

5.37508

=CK(100,300)

18

30

1228.013

96

289.0288

141

33.05862

28

32.75844

87615.152

19

142

508.5571

47

880.1986

106

1181.068

80

134.5152

=CK(100,500)

- CK method, For same k, best route was for cl=500km 87,448 km

20

63

384.1693

71

1548.17

126

1049.886

53

84.62657

87448.663

- Without cluster optimization (MK) delivers 92743km

21

82

21.86973

134

90.11446

128

542.756

4

51.07941

=CK(100,700)

22

64

39.55923

107

504.8411

100

568.1451

44

43.72553