PDA

View Full Version : [SOLVED] Increment number sequence by two conditions



omp001
04-10-2018, 01:24 PM
Hi all. I need help to make a number sequence like below (red numbers), in any column. These numbers will be used with INDIRECT function.

row 1 ~~~> 1
row 2 ~~~> 2 (step =1 ~~~> row 1 + 1)
row 3 ~~~> 7 (step = 5 ~~~> row 2 + 5)
row 4 ~~~> 8 (step =1 ~~~> row 3 + 1)
row 5 ~~~> 13 (step = 5 ~~~> row 4 + 5)
row 6 ~~~> 14 (step =1 ~~~> row 5 + 1)
row 7 ~~~> 19 (step = 5 ~~~> row 6 + 5)
row 8 ~~~> 20 (step =1 ~~~> row 7 + 1)
...
...
...

Thanks in advance to all.

Paul_Hossler
04-10-2018, 06:39 PM
A1 = 1
A2 = 2
A3 = 7
etc.

B2 = IF(ISODD(ROW()),A1+5,A1+1)

Fill down

22009

omp001
04-11-2018, 07:52 AM
Hi, Paul. Thank you for the nice solution.
I used your formula directly in column A, like this: A1 = 1 and your formula in A2 and dragged it down. This generated exactly the numerical sequence I need. Then I used this one in an empty column =INDEX(D$1 : D$100,A1) and dragged it down, so I got the data from column D (which is the goal), using column A as an auxiliary column.
That already solves the problem. Thank you.
To improve this solution, would there be a way to get the data from D1, D2, D7, D8, ... without using an auxiliary column?
Thank you so much.

p45cal
04-13-2018, 04:35 AM
Perhaps:
=INDEX(D$1:D$100,(ROW()/2-1)*6+IF(ISODD(ROW()),4,2))
?

omp001
04-13-2018, 08:34 AM
Perhaps:
=INDEX(D$1:D$100,(ROW()/2-1)*6+IF(ISODD(ROW()),4,2))?

Yes, Sir. Amazing. :bow: :bow: :bow:

Thank you so much.