Super User Asked by nm82 on December 8, 2020

For information, I asked the question in www.clever-excel-forum.de and www.ms-office-forum.net/. I would like to achieve the following:

```
A1 A2 A3 A4 -> row 1
1 1 1 45
2 30
3 30
1 1 4 45
5 30
...
1 1 254 45
1 30
2 30
1 2 3 45
...
1 254 254 45
1 30
2 30
2 1 3 45
...
3 189 12 45 -> up to approx row 250540
```

A2 should set a 1 from line 2 onwards in every three lines until a 254 was reached for the first time in A3. Then with the next setting (after 254 in A3) the 1 must be incremented and jump to 2 (A2). If the 254 in A2 is reached, it has to jump back to 1 and the 1 in A1 jumps to 2.

so far so good (from user: lupo1 from the ms-office-forum):

```
A1: =LET(n;250000;WAHL(SEQUENZ(;4);
WENN(REST(SEQUENZ(n);3)=1;KÜRZEN(SEQUENZ(n;;0;1/254^2)+1);"");
WENN(REST(SEQUENZ(n);3)=1;KÜRZEN(SEQUENZ(n;;0;1/254^1)+1);"");
REST(SEQUENZ(n;;0);254)+1;
INDEX({45.30.30};REST(SEQUENZ(n)-1;3)+1)))
WENN = IF,
MOD = REMAIN,
SEQUENZ = SEQUENCE,
KÜRZEN = TRUNC,
```

One thing doesn’t work yet: Column A2 counts beyond 254, but should always jump back to 1 after 254.

Using the row() number as index, column C is just the remainder after division (modulus) by 254, offset by 1 (ie. 0..253 to 1..254):

```
=MOD(ROW()-1,254)+1
```

Column B is the quotient after integer division by 254 and column A the quotient after integer division by 254^2, both scaled to 1..254 with the modulus and offset by 1:

```
=IF(MOD(ROW()-1,3)=0,MOD(INT((ROW()-1)/254),254)+1,"")
```

and

```
=IF(MOD(ROW()-1,3)=0,MOD(INT((ROW()-1)/(254^2)),254)+1,"")
```

The if statement just ensure the numbers are displayed on every third row.

Answered by Mobus on December 8, 2020

Get help from others!

Recent Answers

- Peter Machado on Why fry rice before boiling?
- Lex on Does Google Analytics track 404 page responses as valid page views?
- Joshua Engel on Why fry rice before boiling?
- Jon Church on Why fry rice before boiling?
- haakon.io on Why fry rice before boiling?

Recent Questions

- How can I transform graph image into a tikzpicture LaTeX code?
- How Do I Get The Ifruit App Off Of Gta 5 / Grand Theft Auto 5
- Iv’e designed a space elevator using a series of lasers. do you know anybody i could submit the designs too that could manufacture the concept and put it to use
- Need help finding a book. Female OP protagonist, magic
- Why is the WWF pending games (“Your turn”) area replaced w/ a column of “Bonus & Reward”gift boxes?

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP