# Thread: Filling a number series in Excel XP (Excel XP)

1. ## Filling a number series in Excel XP (Excel XP)

HI!

I have a user who exported a text delimited file to Excel. This file is over 16, 000 rows! Problem: She needs to fill a column down with the number series:
1
1
1
1
1
2
2
2
2
2
3
3
3
3
3
ETC...

Problem is Excel makes the filled numbers into 9 place decimals. Then through the virture of the auto rounding 'feature' the numbers begin to be off... she needs the numbers to increment down the worksheet, 5 rows for each number. Why does excel decide to make the next number in the fill 3.428571429 rather than 4?

I've tried various cell number formats to no avail.

Any suggestions? Typing this manually would be a nightmare for her!

Thanks!
JC<img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

2. ## Re: Filling a number series in Excel XP (Excel XP)

Surely, there's a better solution, but a quick on is to put =CEILING(ROW()/5,1) into A1 and fill that down 16000 rows, copy, paste special values. HTH

3. ## Re: Filling a number series in Excel XP (Excel XP)

You can also just put down 5 rows of 1 (A1 - A5 eg)
Then in A6 enter +1+A1.
copy A6 down as many rows as needed and you have your pattern.
Copy and paste-specialvalues

Steve

4. ## Re: Filling a number series in Excel XP (Excel XP)

Thanks guys... the cell ref + 1 did the trick quite nicely.

Now.. if I just understood why in the heck Excel wants to go from 3 to 3.428571429. I'm sure there is math logic in there somewhere... but since I left Accounting years ago for Tech I just don't do math anymore!

JC<img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

5. ## Re: Filling a number series in Excel XP (Excel XP)

When you select two cells below each other and fill down, Excel creates a linear series that increases with the difference between the first two cells.
When you select more than two cells below each other and fill down, Excel tries to extrapolate the selected values by a linear series, i.e. it creates new values with a constant difference with the best fit to the data you entered. It doesn't try to discover the pattern in the data you entered.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•