Skip
# excel numbering questin

Post

# excel numbering questin

May 22, 2014 4:30 PM Subscribe

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?

So it would look like this:

1

1

1

2

2

2

3

3

3

Is there a formula that would automate this?

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]

posted by yonglin at 4:38 PM on May 22, 2014 [1 favorite]

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]

posted by michaelh at 4:41 PM on May 22, 2014 [3 favorites]

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]

posted by If only I had a penguin... at 4:41 PM on May 22, 2014 [1 favorite]

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

posted by ssg at 4:54 PM on May 22, 2014

Awesome, those get the job done. Thanks everyone!

posted by SpacemanStix at 5:02 PM on May 22, 2014

posted by SpacemanStix at 5:02 PM on May 22, 2014

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]

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]

You are not logged in, either login or create an account to post comments

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