+ Reply to Thread
Results 1 to 3 of 3

Increment Number Every Nth Cell

Hybrid View

  1. #1
    Registered User
    Join Date
    11-26-2013
    Location
    New Orleans, LA
    MS-Off Ver
    Excel 2008
    Posts
    1

    Increment Number Every Nth Cell

    Hello-

    I have a spreadsheet with 2100 rows. I am trying to fill one column with a numbering system that fills the same number in for 21 cells and then increases by one. For example:

    TRIAL WHO SICK?
    1 10 TRUE
    1 5 FALSE
    1 13 FALSE
    1 17 FALSE
    1 7 FALSE
    1 2 FALSE
    1 6 FALSE
    1 4 TRUE
    1 8 FALSE
    1 15 FALSE
    1 11 FALSE
    1 19 FALSE
    1 14 FALSE
    1 16 FALSE
    1 3 FALSE
    1 0 FALSE
    1 12 TRUE
    1 9 FALSE
    1 18 TRUE
    1 1 FALSE

    2 4 FALSE
    2 0 FALSE
    2 8 FALSE
    2 11 FALSE
    2 18 FALSE
    2 3 FALSE
    2 14 FALSE
    2 1 FALSE
    2 17 FALSE
    2 15 FALSE
    2 7 TRUE
    2 13 FALSE
    2 19 TRUE
    2 2 FALSE
    2 9 FALSE
    2 10 FALSE
    2 12 TRUE
    2 16 FALSE
    2 5 FALSE
    2 6 FALSE

    3 12 TRUE
    3 9 FALSE
    3 8 FALSE
    3 16 FALSE
    3 17 FALSE
    3 11 FALSE
    3 14 FALSE
    3 19 FALSE
    3 7 FALSE
    3 4 FALSE
    3 3 FALSE
    3 5 FALSE
    3 0 FALSE
    3 1 FALSE
    3 6 FALSE
    3 13 FALSE
    3 18 FALSE
    3 15 FALSE
    3 2 FALSE
    3 10 FALSE


    However, I did the first column manually and wish to fill in my "trial" column for all 100 trials. Any suggestions? Thanks!

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Increment Number Every Nth Cell

    =int((rows(a$1:a1)-1)/21)+1
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Contributor
    Join Date
    12-16-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    109

    Re: Increment Number Every Nth Cell

    another way would be to insert a hidden column

    in your first column
    =IF(B1=21, A1+1, A1)

    in your second hidden column
    =IF(A1=A2,B1+1,0)

    http://www.easyexcelanswers.com

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. cell number increment
    By edypat in forum Excel General
    Replies: 1
    Last Post: 11-24-2007, 05:27 PM
  2. number cell increment +1 when dragging down box
    By Johnny Cake in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 AM
  3. how to increment number in a cell?
    By senceremre in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-15-2005, 10:58 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1