hello, i need a formula to create a sequence in rows and columns ex: 1 2 3 3 2 1 and 1 2 3 3 2 1
B barabont New Member Feb 18, 2017 #1 hello, i need a formula to create a sequence in rows and columns ex: 1 2 3 3 2 1 and 1 2 3 3 2 1
B bosco_yip Excel Ninja Feb 19, 2017 #2 1] Sequence in column formula : =ABS(MOD(ROWS($1:1)-1,3)+1-4*(MOD(INT((ROWS($1:1)-1)/3),2))) 2] Sequence in row formula : =ABS(MOD(COLUMNS($A:A)-1,3)+1-4*(MOD(INT((COLUMNS($A:A)-1)/3),2))) Regards Bosco Attachments Sequence in rows columns.xlsx 10.1 KB · Views: 1
1] Sequence in column formula : =ABS(MOD(ROWS($1:1)-1,3)+1-4*(MOD(INT((ROWS($1:1)-1)/3),2))) 2] Sequence in row formula : =ABS(MOD(COLUMNS($A:A)-1,3)+1-4*(MOD(INT((COLUMNS($A:A)-1)/3),2))) Regards Bosco
Monty Well-Known Member Feb 19, 2017 #3 Hey not sure your requirment but another way without formula. Fill a column with a series of numbers Select the first cell in the range that you want to fill. Type the starting value for the series. Type a value in the next cell to establish a pattern. ... Select the cells that contain the starting values. ... Drag the fill handle across the range that you want to fill.
Hey not sure your requirment but another way without formula. Fill a column with a series of numbers Select the first cell in the range that you want to fill. Type the starting value for the series. Type a value in the next cell to establish a pattern. ... Select the cells that contain the starting values. ... Drag the fill handle across the range that you want to fill.
B barabont New Member Feb 19, 2017 #4 bosco_yip said: View attachment 38676 1] Sequence in column formula : =ABS(MOD(ROWS($1:1)-1,3)+1-4*(MOD(INT((ROWS($1:1)-1)/3),2))) 2] Sequence in row formula : =ABS(MOD(COLUMNS($A:A)-1,3)+1-4*(MOD(INT((COLUMNS($A:A)-1)/3),2))) Regards Bosco Click to expand... thank you for this. how would i change this to go up to 5 then back down? so, 1 2 3 4 5 5 4 3 2 1 do i have to change the divisor?
bosco_yip said: View attachment 38676 1] Sequence in column formula : =ABS(MOD(ROWS($1:1)-1,3)+1-4*(MOD(INT((ROWS($1:1)-1)/3),2))) 2] Sequence in row formula : =ABS(MOD(COLUMNS($A:A)-1,3)+1-4*(MOD(INT((COLUMNS($A:A)-1)/3),2))) Regards Bosco Click to expand... thank you for this. how would i change this to go up to 5 then back down? so, 1 2 3 4 5 5 4 3 2 1 do i have to change the divisor?
B bosco_yip Excel Ninja Feb 19, 2017 #5 barabont said: thank you for this. how would i change this to go up to 5 then back down? so, 1 2 3 4 5 5 4 3 2 1 do i have to change the divisor? Click to expand... Try...... =ABS(MOD(ROWS($1:1)-1,5)+1-6*(MOD(INT((ROWS($1:1)-1)/5),2))) Regards Bosco
barabont said: thank you for this. how would i change this to go up to 5 then back down? so, 1 2 3 4 5 5 4 3 2 1 do i have to change the divisor? Click to expand... Try...... =ABS(MOD(ROWS($1:1)-1,5)+1-6*(MOD(INT((ROWS($1:1)-1)/5),2))) Regards Bosco