Join 3,512 readers in helping fund MetaFilter (Hide)


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?
posted by SpacemanStix to Technology (7 answers total) 1 user marked this as a favorite
 
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


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 [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 [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 [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


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


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 [2 favorites]


« Older I am going to be getting soap ...   |  As sort of a follow up to anot... Newer »

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