CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
vbalearner
vbalearner

Reputation: 133

Changing the numbers in two different intervals

Currently I am using the following formula: 

=1 + (FLOOR((ROW()-2);5) / 5) + (FLOOR((ROW()-2);5) / 5) * 6

to change the numbers by 7 in different intervals and everything is working fine and you can see column A. But numbers should change by 15 for every 5th change and again the rest 4 changes should change by 7 (see in column B):

enter image description here.

Is it possible to build a formula like that?

Upvotes: 2

Views: 83

Answers (2)

Jerry
Jerry

Reputation: 71578

I changed your formula a bit to be shorter and a little safer:

=1+(INT((ROWS($C$1:C2)-2)/5)*7)+INT((ROWS($C$1:C2)-2)/20)*8

The first part alone would be =1+(INT((ROWS($C$1:C2)-2)/5)*7) and the added part takes care of the changes at the 5th change.

Hopefully, that will suit your needs better!

Addendum:

Couple of notes regarding the formula:

INT((ROWS($C$1:C2)-2)/5)*7
                      ^  ^
                      a  b

a gives the frequency when 'change' of b has to be applied. So in the above, 7 will be added after 5 rows, and extending the logic to the other part of the final formula, 8 is being added on top of the first 7 (total 15) added every 20 rows (i.e. after 4 changes each of 5 rows).

Upvotes: 3

asongtoruin
asongtoruin

Reputation: 10359

I believe what you are looking for boils down to adding another 8 every 20th row. This can be achieved by adding FLOOR((ROW()-2)/20;1)*8 to your formula.

Upvotes: 1

Related Questions