Filling a number series in Excel XP (Excel XP)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote