Simple formula = every 3rd cell

[Dan_D]

Hi All,

I seem to be stuck on the most simple formula and hope someone can kindly please help.

I have a list of data in column A and in column B. I simply want to have a formula that increase the reference cell by three in each instance.

For example

Cell B1 = A1
Cell B2 = A3
Cell B3 = A5
Cell B3 = A7

and so on, yet if i try to auto fill this formula down from here on it goes back to A2 in cell B4.

Any suggestions are appreciated.

Thank you in advance.

 

[Aladin Akyurek]

Hi All,

I seem to be stuck on the most simple formula and hope someone can kindly please help.

I have a list of data in column A and in column B. I simply want to have a formula that increase the reference cell by three in each instance.

For example

Cell B1 = A1
Cell B2 = A3
Cell B3 = A5
Cell B3 = A7

and so on, yet if i try to auto fill this formula down from here on it goes back to A2 in cell B4.

Any suggestions are appreciated.

Thank you in advance.

B1, enter and copy down:

=INDEX($A$1:$A$9,3*(ROWS($B$1:B1)-1))

 

[Dan_D]

Many thanks for your assistance. Can you please the formula to me as I would like to use this formula again, however using an increment of 4 cells

A1
A4
A7
A10
A13

Thank you.

 

[Aladin Akyurek]

Many thanks for your assistance. Can you please the formula to me as I would like to use this formula again, however using an increment of 4 cells

A1
A4
A7
A10
A13

Thank you.

=INDEX($A$1:$A$90,4*(ROWS($B$1:B1)-1))

Or you can enter 4 in a cell of its own, say, in E1, and invoke:

=INDEX($A$1:$A$90,$E$1*(ROWS($B$1:B1)-1))

 

[Dan_D]

Many thanks for your prompt response, however the formula is not working correctly at the moment.

According to the formula

=INDEX($A$1:$A$90,4*(ROWS($B$1:B1)-1))

Is it giving the following results.

B1 = A1
B2 = A4
B3 = A8 (should be A7)
B4 = A12 (should be A10)

Any advice on how to resolve this please?

 

[Aladin Akyurek]

Many thanks for your prompt response, however the formula is not working correctly at the moment.

According to the formula

=INDEX($A$1:$A$90,4*(ROWS($B$1:B1)-1))

Is it giving the following results.

B1 = A1
B2 = A4
B3 = A8 (should be A7)
B4 = A12 (should be A10)

Any advice on how to resolve this please?

Are you trying to do an arbitrary number of every 4th value intermixed with an arbitrary number of every 3rd value? If so, how do we know when start and when end?

 

[Dan_D]

Sorry for the confusion I have fixed the issue.

I put your formula in B1 and then in cell B2 changed the following

=INDEX($A$1:$A$9,3*(ROWS($B$1:B1)-1))

to

=INDEX($A$2:$A$9,3*(ROWS($B$1:B1)-1))

now it works thank-you.

 

[Aladin Akyurek]

Sorry for the confusion I have fixed the issue.

I put your formula in B1 and then in cell B2 changed the following

=INDEX($A$1:$A$9,3*(ROWS($B$1:B1)-1))

to

=INDEX($A$2:$A$9,3*(ROWS($B$1:B1)-1))

now it works thank-you.

Great. Thanks for the feedback.

 

[Dan_D]

Thank you kindly for your assistance.