1

I have following scenario.

I have numbers grouped by 8 same values and then I want next 8 values to raise by 1.

Example:

4
4
4
4
4
4
4
4
5
5
5
5
5
5
5
5

I tried few suggestions, but I can't get it to work. :(

When I just drag selected values, it does the work, but ads few extra values.

2
  • 3
    Use =const1+INT((ROW()+const2)/8). For example while starting from A1 const1=3, const2=7.
    – Akina
    Oct 17, 2019 at 12:10
  • Working like a charm. Thanks a ton!
    – Mason
    Oct 17, 2019 at 12:23

2 Answers 2

2

You could use =INT((ROW(E1)-1)/5)+1

In the formula, the number 5 indicates to increment the numbers every 5 rows, you can change it to your need

Edit:

These formulas was advised by Duke Bouvier: INT((ROWS(E1:E$1)-1)/8)+1 and INT((ROWS(E1:E$1)-1)/8)+1 each one avoids to hard-code the starting line number into the formula. Thank you Duke.

3
  • 1
    You can refine this to INT((ROW(E1)-ROW(E$1)/8)+1 which avoids having to hard-code the starting line number into the formula, or similarly INT((ROWS(E1:E$1)-1)/8)+1 Oct 17, 2019 at 14:22
  • @DukeBouvier Your suggested formulas was included in the answer. Oct 17, 2019 at 15:37
  • @Duke Bouvier Thank you. = INT((ROWS(C1382:C$1382)-1)/8)+1 I don't know if I managed to figure out those formulas in the meantime, but yours worked from first try. Thank you Haroldo as well. :)
    – Mason
    Oct 18, 2019 at 15:07
1

What I usually do is (as per picture) just use a very standard increment formula but with references to a cell 8 cells above rather than 1 cell above. You can then extend it down as far as you want by copying the last cell or dragging the handle on the bottom right of the cell that you can see when it is selected.

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .