1

I am looking for a formula to generate the sequence down row B starting from B3. I would like the sequence to be: every fourth term +8 and the following three terms to be -1 sequentially, ie) 11,10,9,8,19,18,17,16,27,26,25,24,35,34,33,32....

Any ideas?

I have tried =IF(MOD(B3,4)=0,($B$3:B3)+8,B3-1) however this only works for the first few terms as the add 8 always occurs to B3 rather than to B7, B11... in turn

2
  • 1
    You are missing the MAX() from my formula. Jun 22, 2017 at 16:43
  • I have tried your formula also many times and still not working
    – Emily G
    Jun 22, 2017 at 16:43

3 Answers 3

0

Just enter

=11-MOD(ROW()-3,4)+INT((ROW()-3)/4)*8

in B3 and drag/copy down:

enter image description here

0

Like Scott said you have to use the following:
In B3 write 11
In B4 write:

=IF(MOD($B3,4)=0,Max($B$3:$B3)+8,$B3-1)

and drag it down
Mod($B3,4)=0 will occur every fourth term
Max($B$3:$B3) will return the highest value to which you will add 8

enter image description here

In case you need to start with any value and the sequence is like you said the following 3 values B3-1 and the fourth value B3+8 you can use:
Just start in B3 with the initial value and in B4 write:

=IF(MOD(COUNT($B$3:B3),4)=0,MAX($B$3:$B3)+8,$B3-1)

and drag it down
Mod(Count(),4)=0 will correspond to the following value + 8

0

In B3 enter 11, in B4 enter:

=IF(MOD(B3,4)=0,B3+11,B3-1)

and copy down:

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .