Relative referencing skipping reference rows every nth row

[RudyShare]

Good day

I have a requirement and hope you can help.

What I want is something like this:

Formula Row	Reference Row
1	1
2	1
3	1
4	4
5	4
6	4
7	7
8	7
9	7

How would I do this?

 

[Fluff]

Hi & welcome to MrExcel.
How about

=TOCOL(IF(SEQUENCE(,3),SEQUENCE(5,,,3)))

 

[RudyShare]

Hi & welcome to MrExcel.
How about

=TOCOL(IF(SEQUENCE(,3),SEQUENCE(5,,,3)))

Thank you for the reply. Unfortunately this is not not exactly what I need.

I'm looking to return the actual value in Col A x amount of times (3 times in the example below) into Col B. Your formula gives fixed values as in Col C below.

5​	5​	1​
	5​	1​
	5​	1​
8​	8​	4​
	8​	4​
	8​	4​
1​	1​	7​
	1​	7​
	1​	7​
		10​
		10​
		10​
		13​
		13​
		13​

 

[Fluff]

Will you have the 2 blank cells between the numbers in col A, or not?

 

[RudyShare]

Will you have the 2 blank cells between the numbers in col A, or not?

Hi

Yes they will be blank

 

[Fluff]

Ok, how about

Fluff.xlsm
	A	B	C
1
2	5		5
3			5
4			5
5	8		8
6			8
7			8
8	1		1
9			1
10			1
Data

Cell Formulas
Range	Formula
C2:C10	C2	=LET(f,FILTER(A2:A100,A2:A100<>""),TOCOL(IF(SEQUENCE(,3),f)))
Dynamic array formulas.

 

[RudyShare]

Ok, how about

Fluff.xlsm
	A	B	C
1
2	5		5
3			5
4			5
5	8		8
6			8
7			8
8	1		1
9			1
10			1
Data

Cell Formulas
Range	Formula
C2:C10	C2	=LET(f,FILTER(A2:A100,A2:A100<>""),TOCOL(IF(SEQUENCE(,3),f)))
Dynamic array formulas.

This is working well thank you.

 

[Fluff]

You're welcome & thanks for the feedback.