textjoin high and low of a sequence

[ExcelNewbie2020]

sirs, what formula to use to textjoin the high and low of a sequence. thank you

Excel.xlsm
	A	B
1	SOURCE	RESULT
2	65	65-60
3	64
4	63
5	62
6	61
7	60
8	55	55
9	50	50-48
10	49
11	48
12	35	35-34
13	34
14	29	29-23
15	28
16	27
17	26
18	25
19	24
20	23
Sheet9

 

[Peter_SSs]

One way

24 03 02.xlsm
	A	B
1	SOURCE	RESULT
2	65	65-60
3	64
4	63
5	62
6	61
7	60
8	55	55
9	50	50-48
10	49
11	48
12	35	35-34
13	34
14	29	29-23
15	28
16	27
17	26
18	25
19	24
20	23
21
High Low

Cell Formulas
Range	Formula
B2:B20	B2	=IF(A2=N(A1)-1,"",LET(n,MATCH(TRUE,(A2:A$20-A3:A$21)<>1,0),A2&IF(n=1,"",-(A2-n+1))))

 

[ExcelNewbie2020]

One way

24 03 02.xlsm
	A	B
1	SOURCE	RESULT
2	65	65-60
3	64
4	63
5	62
6	61
7	60
8	55	55
9	50	50-48
10	49
11	48
12	35	35-34
13	34
14	29	29-23
15	28
16	27
17	26
18	25
19	24
20	23
21
High Low

Cell Formulas
Range	Formula
B2:B20	B2	=IF(A2=N(A1)-1,"",LET(n,MATCH(TRUE,(A2:A$20-A3:A$21)<>1,0),A2&IF(n=1,"",-(A2-n+1))))

it did worked.. thanks.. by the way, i am just curious..can it be done by an array formula?

 

[kvsrinivasamurthy]

ARRAY formula in B2

=IF(LARGE(IF($A$2:$A$16<CEILING($A2,10),$A$2:$A$16,""),1)=$A2,$A2&"-"&SMALL(IF($A$2:$A$16>FLOOR($A2,10),$A$2:$A$16,""),1),"")

copied down.

Sourse	Formula
65	65-61
64
63
62
61
58	58-53
55
53
47	47-42
46
44
42
36	36-33
34
33

 

[Peter_SSs]

it did worked.. thanks..

You're welcome. Thanks for the confirmation.

.. can it be done by an array formula?

It is an array formula.

ARRAY formula in B2

=IF(LARGE(IF($A$2:$A$16<CEILING($A2,10),$A$2:$A$16,""),1)=$A2,$A2&"-"&SMALL(IF($A$2:$A$16>FLOOR($A2,10),$A$2:$A$16,""),1),"")

copied down.

Only thing is that your formula applied to the OP's sample data does not produce the results requested.

 

[Eric W]

by the way, i am just curious..can it be done by an array formula?

Do you mean a spill formula? Maybe:

Book1
	A	B
1	SOURCE	RESULT
2	65	65-60
3	64
4	63
5	62
6	61
7	60
8	55	55
9	50	50-48
10	49
11	48
12	35	35-34
13	34
14	29	29-23
15	28
16	27
17	26
18	25
19	24
20	23
21
Sheet5

Cell Formulas
Range	Formula
B2:B20	B2	=LET(d,A2:A20,b,A1:A19,a,A3:A21,st,IF(IFERROR(d<b-1,1),d),en,IF(d>a+1,d),sc,SEQUENCE(COUNT(st)),stl,LARGE(st,sc),enl,LARGE(en,sc),z,INDEX(enl,MATCH(d,stl,0)),IF(ISNUMBER(MATCH(d,st,0)),IF(d=z,d,d&"-"&z),""))
Dynamic array formulas.

This should work, with a lot of caveats: all integers, no repeats, strictly decreasing.

 

[Peter_SSs]

it did worked.. thanks..

Looking again, I see that my earlier formula would fail if the last value in the range was not part of the sequence above. For example if A20 was, say, 16. See col D below
The correction is in column C

Do you mean a spill formula?

If Eric is right about what you were asking, then the adaptation of my method is in col B.

24 03 02.xlsm
	A	B	C	D
1	SOURCE	Spill	Correction	Original
2	65	65-60	65-60	65-60
3	64
4	63
5	62
6	61
7	60
8	55	55	55	55
9	50	50-48	50-48	50-48
10	49
11	48
12	35	35-34	35-34	35-34
13	34
14	29	29-24	29-24	29-24
15	28
16	27
17	26
18	25
19	24
20	16	16	16	#N/A
21
High Low

Cell Formulas
Range	Formula
B2:B20	B2	=LET(a,A2:A20,E,INDEX(a,ROWS(a)),F,OFFSET(E,1,0),BYROW(a,LAMBDA(r,IF(r=N(OFFSET(r,-1,0))-1,"",LET(n,IFNA(MATCH(TRUE,r:E-OFFSET(r,1,0):F<>1,0),1),r&IF(n=1,"",-(r-n+1)))))))
C2:C20	C2	=IF(A2=N(A1)-1,"",LET(n,IFNA(MATCH(TRUE,(A2:A$20-A3:A$21)<>1,0),1),A2&IF(n=1,"",-(A2-n+1))))
D2:D20	D2	=IF(A2=N(A1)-1,"",LET(n,MATCH(TRUE,(A2:A$20-A3:A$21)<>1,0),A2&IF(n=1,"",-(A2-n+1))))
Dynamic array formulas.

 

[ExcelNewbie2020]

Looking again, I see that my earlier formula would fail if the last value in the range was not part of the sequence above. For example if A20 was, say, 16. See col D below
The correction is in column C


If Eric is right about what you were asking, then the adaptation of my method is in col B.

24 03 02.xlsm
	A	B	C	D
1	SOURCE	Spill	Correction	Original
2	65	65-60	65-60	65-60
3	64
4	63
5	62
6	61
7	60
8	55	55	55	55
9	50	50-48	50-48	50-48
10	49
11	48
12	35	35-34	35-34	35-34
13	34
14	29	29-24	29-24	29-24
15	28
16	27
17	26
18	25
19	24
20	16	16	16	#N/A
21
High Low

Cell Formulas
Range	Formula
B2:B20	B2	=LET(a,A2:A20,E,INDEX(a,ROWS(a)),F,OFFSET(E,1,0),BYROW(a,LAMBDA(r,IF(r=N(OFFSET(r,-1,0))-1,"",LET(n,IFNA(MATCH(TRUE,r:E-OFFSET(r,1,0):F<>1,0),1),r&IF(n=1,"",-(r-n+1)))))))
C2:C20	C2	=IF(A2=N(A1)-1,"",LET(n,IFNA(MATCH(TRUE,(A2:A$20-A3:A$21)<>1,0),1),A2&IF(n=1,"",-(A2-n+1))))
D2:D20	D2	=IF(A2=N(A1)-1,"",LET(n,MATCH(TRUE,(A2:A$20-A3:A$21)<>1,0),A2&IF(n=1,"",-(A2-n+1))))
Dynamic array formulas.

yes, i mean a spill array formula.. thanks man.. really appreciated

 

[Peter_SSs]

Glad we could help.