Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    Ames, Iowa, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    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. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #4
    Lounger
    Join Date
    Apr 2001
    Location
    Ames, Iowa, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •