Offset Function

[c0087]

How would I edit this function so that I could get every 3rd row, then 5th row, 3rd row, 5th row, etc...

=OFFSET($A$1,(ROW()-1)*3,0)

 

[Cubist]

How about this?

=LET(m,MOD(ROW(A2:A10),2),FILTER(A2:A10,m=1))

 

[c0087]

How about this?

=LET(m,MOD(ROW(A2:A10),2),FILTER(A2:A10,m=1))

It only returns every 2nd row. Same as if i was using =OFFSET($A$1,(ROW()-1)2,0).

I'd prefer it in the OFFSET function so that it easier to drag around, too

 

[Cubist]

Can you show an example of what you're trying to achieve?

 

[Cubist]

If I understand correctly, you want a formula that you can drag to repeat the 3rd and 5th value.

Book1
	A	B
1	A
2	B	E
3	C	C
4	D	E
5	E	C
6	F	E
7	G	C
8	H	E
9	I	C
10	J	E
11	K	C
12	L	E
13	M	C
14	N	E
15	O	C
16	P	E
17	Q	C
18	R	E
19	S	C
20	T	E
21	U	C
22	V	E
23	W	C
24	X	E
25	Y	C
26	Z	E
27	[	C
28	\	E
29	]	C
30	^	E
31	_	C
32	`	E
33	a	C
34	b	E
35	c	C
36	d	E
37	e	C
38	f	E
39	g	C
40	h	E
41	i	C
42	j	E
43	k	C
44	l	E
45	m	C
46	n	E
47	o	C
48	p	E
49	q	C
50	r	E
Sheet1

Cell Formulas
Range	Formula
B2:B50	B2	=LET(a,OFFSET($A$1,2,0),b,OFFSET(a,2,0),IF(MOD(ROW(),2),a,b))

 

[c0087]

something inside the OFFSET function that would return every 3rd row, then 5th row, etc

1	1
2	4
3	9
4	12
5	17
6	20
7	25
8
9
10

 

[c0087]

If I understand correctly, you want a formula that you can drag to repeat the 3rd and 5th value.

Book1
	A	B
1	A
2	B	E
3	C	C
4	D	E
5	E	C
6	F	E
7	G	C
8	H	E
9	I	C
10	J	E
11	K	C
12	L	E
13	M	C
14	N	E
15	O	C
16	P	E
17	Q	C
18	R	E
19	S	C
20	T	E
21	U	C
22	V	E
23	W	C
24	X	E
25	Y	C
26	Z	E
27	[	C
28	\	E
29	]	C
30	^	E
31	_	C
32	`	E
33	a	C
34	b	E
35	c	C
36	d	E
37	e	C
38	f	E
39	g	C
40	h	E
41	i	C
42	j	E
43	k	C
44	l	E
45	m	C
46	n	E
47	o	C
48	p	E
49	q	C
50	r	E
Sheet1

Cell Formulas
Range	Formula
B2:B50	B2	=LET(a,OFFSET($A$1,2,0),b,OFFSET(a,2,0),IF(MOD(ROW(),2),a,b))

Sorry for the confusion. I don't want it to repeat the 3rd and 5th row, but keep taking every 3rd and 5th row as i drag down

 

[Cubist]

If you're ok with a helper column.

Book1
	A	B	C	D	E
1	Data	Helper	Result
2	1	0	1
3	2	3	4
4	3	8	9
5	4	11	12
6	5	16	17
7	6	19	20
8	7	24	25
9	8	27	28
10	9	32	33
11	10	35	36
12	11	40	41
13	12	43	44
14	13	48	49
15	14	51
16	15	56
17	16	59
18	17	64
19	18	67
20	19	72
21	20	75
22	21	80
23	22	83
24	23	88
25	24
26	25
27	26
28	27
29	28
30	29
31	30
32	31
Sheet1

Cell Formulas
Range	Formula
A2:A51	A2	=SEQUENCE(50)
C2:C14	C2	=OFFSET($A$2,B2,0)
B3:B24	B3	=IF(MOD(ROW(),2),B2+3,B2+5)
Dynamic array formulas.

 

[c0087]

If you're ok with a helper column.

Book1
	A	B	C	D	E
1	Data	Helper	Result
2	1	0	1
3	2	3	4
4	3	8	9
5	4	11	12
6	5	16	17
7	6	19	20
8	7	24	25
9	8	27	28
10	9	32	33
11	10	35	36
12	11	40	41
13	12	43	44
14	13	48	49
15	14	51
16	15	56
17	16	59
18	17	64
19	18	67
20	19	72
21	20	75
22	21	80
23	22	83
24	23	88
25	24
26	25
27	26
28	27
29	28
30	29
31	30
32	31
Sheet1

Cell Formulas
Range	Formula
A2:A51	A2	=SEQUENCE(50)
C2:C14	C2	=OFFSET($A$2,B2,0)
B3:B24	B3	=IF(MOD(ROW(),2),B2+3,B2+5)
Dynamic array formulas.

Thanks, but I need it in one formula if possible

 

[Cubist]

Here's the best I can come up with so far that does not use a helper column and let you drag. Only draw back is you have to reference the first value A1 yourself so it knows where to start.

Book1.xlsx
	A	B
1	1	1
2	2	4
3	3	9
4	4	12
5	5	17
6	6	20
7	7	25
8	8	28
9	9	33
10	10	36
11	11	41
12	12	44
13	13	49
14	14
15	15
16	16
17	17
18	18
19	19
20	20
21	21
22	22
23	23
24	24
25	25
26	26
27	27
28	28
29	29
30	30
31	31
32	32
33	33
34	34
35	35
36	36
37	37
38	38
39	39
40	40
41	41
42	42
43	43
44	44
45	45
46	46
47	47
48	48
49	49
50	50
Sheet1

Cell Formulas
Range	Formula
B1	B1	=A1
B2:B13	B2	=LET(a,INDIRECT(CELL("address",INDIRECT("A"&MATCH(B1,$A$1:$A$50,0)))),OFFSET(a,IF(MOD(ROW(),2),5,3),0))