CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
user3186707
user3186707

Reputation: 101

Nested IF Excel formula

I am currently using the following formula i.e. =IF(COUNTIF($A$1:A2,A2)>4,A2+1,A2) to change the number when I drag this formula downsdie of the rows.

For Example: in this case for every five rows number will change i.e. A1 to A5 it will 1 and A6 to A10 it will be 2 and A11 to A15 it will be 3 etc.

Just wanted to know is it possible to extend the same formula, so along with adding 1 number for every five rows it should also skip 2 numbers for every 60 rows.

For Example: if the 60 row is number 12, then 61st row should be 15 and 120 row will be 26 and 121 row should be 124 etc.

Can someone please help me with this formula?

Thanks for your help in advance.

Upvotes: 0

Views: 127

Answers (1)

shawnt00
shawnt00

Reputation: 17915

Number starts at one.

Then get the cell's row number and subtract one. Divide that number by 5 and discard the fractional part (or the remainder). So numbers from 0 to 4 (which are rows 1 through 5) all get an increment of 0, 5 to 9 get 1, and so on. Similar logic with multiples of 60 except that the counting is doubled.

=1 + floor((row()-1)/5, 1) + floor((row()-1)/60, 1) * 2

Upvotes: 1

Related Questions