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.

  • 3
    Use =const1+INT((ROW()+const2)/8). For example while starting from A1 const1=3, const2=7. – Akina Oct 17 '19 at 12:10
  • Working like a charm. Thanks a ton! – Mason Oct 17 '19 at 12:23
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.

| improve this answer | |
  • 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 – Duke Bouvier Oct 17 '19 at 14:22
  • @DukeBouvier Your suggested formulas was included in the answer. – Haroldo Payares Salgado Oct 17 '19 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 '19 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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.