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:


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:


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

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]

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]

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]

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

Awesome, those get the job done. Thanks everyone!
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]

« Older Environmentally-friendly craft soap making   |   How "woo"-y is yoga teacher training? Newer »
This thread is closed to new comments.