excel numbering questin

May 22, 2014 4:30 PM

In excel, I want to increase a number in sequential rows by one every three numbers. Is there a way to do this quickly, for about 5000 rows?

So it would look like this:

1
1
1
2
2
2
3
3
3

Is there a formula that would automate this?

posted by SpacemanStix to Technology (7 answers total)

Best answer: Put the following values in:

1
1.3333333
1.6666666
2
2.3333333
2.6666666
3
[...]

And then format the cell to not show the decimal part.

To avoid rounding errors, you might create a hidden column that goes 3,4,5,6 next to it and then set the value of the cell you want to show to be x/3.
posted by Hatashran at 4:37 PM on May 22, 2014

Best answer: You can increase by 1/3 in each row and use trunc to trim the decimals!
posted by yonglin at 4:38 PM on May 22, 2014 [1 favorite]

Best answer: Make the first three cells 1 and then have each cell do the value three cells back plus one.
posted by michaelh at 4:41 PM on May 22, 2014 [3 favorites]

Best answer: I would create one column that's a count (call it column A). Then next to it put a column that is (TRUNC(A/3)+1). Look up the TRUNC function, you might need to specify 0 decimimal places. Then cut the column and paste special as values. Once you've done that you can delete column A.
posted by If only I had a penguin... at 4:41 PM on May 22, 2014 [1 favorite]

Best answer: TRUNC((ROW()/3)+2/3) will do this. You will need to adjust the offset (+2/3) if you don't want to start with row 1 as 1.
posted by ssg at 4:54 PM on May 22, 2014

Response by poster: Awesome, those get the job done. Thanks everyone!
posted by SpacemanStix at 5:02 PM on May 22, 2014

Best answer: Here's another option.

1. Type in these formulae:

A1 = 1
A2 = 1
A3 = 1
A4 = A1+1

2. Click and drag cell A4 down 5000 rows.
posted by Salvor Hardin at 6:18 PM on May 22, 2014 [2 favorites]