CODEWITHSUNDEEP
excelexcel-formula
Niles
Niles

Reputation: 121

Build a sequence of numbers

My goal is to build a automatic coordinates file from a X and Y pitch for X and Y steps. Let say, X pitch = 2 (mm) Y pitch = 1 (mm) X steps = 10 and Y steps = 10

My file should like something like this

    1, 0, 0
    2, 2, 0
    3, 4, 0
    4, 6, 0
    5, 8, 0        
    6, 10, 0
    7, 12, 0
    8, 14, 0
    9, 16, 0
    10, 18, 0
    11, 0, 1
    etc 
    (till 100)

With the sequence function I managed too build to first column of numbers =SEQUENCE(L1*L2;1;1;1) L1=Xsteps L2=Ysteps

Now I am struggeling to build the X and Y column While X is repeating after every step for 10 times, Y is only incrementing every 10 times.

I would like to automate it, because in real life it's never a nice round number. But how?

Upvotes: 0

Views: 192

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34255

If you have Excel 365, you can use Let with Sequence:

=LET(rows,Xsteps*Ysteps,
seq,SEQUENCE(rows,1,0),
column1,seq+1,
column2,MOD(seq,Xsteps)*Xpitch,
column3,INT(seq/Xsteps)*Ypitch,
CHOOSE({1,2,3},column1,column2,column3))

enter image description here

The four cells A2, B2, C2 and D2 are assigned names using the Name Manager based on the names in A1, B1, C1 and D1 respectively.

Upvotes: 0

Dominique
Dominique

Reputation: 17499

This answer is maybe not complete, but I started with row:

1     0             0
=A1+1 =MOD(B1+2,20) =IF(MOD(A2,10)=0,C1+$D$1,C1)

This creates following list:

1   0   0   10308
2   2   0
3   4   0
4   6   0
5   8   0
6   10  0
7   12  0
8   14  0
9   16  0
10  18  10308
11  0   10308

Upvotes: 1

Related Questions