+ Reply to Thread
Results 1 to 3 of 3

Count until a number is found......

  1. #1
    Registered User
    Join Date
    04-14-2007
    Location
    Phoenix AZ
    Posts
    32

    Count until a number is found......

    I want to be able to count the number of cells until a number is found.

    The number range is 1 -35 and there are 5 columns. I want to be able to search a column $B1 until it finds the number 1, then return the number of rows that the number "skipped" in another column. I would like the function to loop until 1 - 35 are checked and updated.
    My search field would be a lottery number history.... 5 columns. My return field would be 5 columns and 35 rows returning how many rows were skipped for each number in each column. Like this, but this is a count of how many times the number occured per column.

    1 24 0 0 0 0
    2 19 0 0 0 0
    3 21 9 0 0 0
    4 19 10 1 0 0
    5 10 9 2 0 0
    6 20 11 0 0 0
    7 7 6 5 1 0
    8 6 11 5 2 0
    9 3 13 3 3 0
    10 7 12 6 3 0
    11 4 10 7 2 0
    12 5 3 6 2 0
    13 1 10 7 1 1
    14 4 6 9 5 0
    15 3 12 14 3 0
    16 3 6 11 6 1
    17 2 5 13 7 2
    18 1 8 8 6 2
    19 2 3 8 12 2
    20 2 7 8 8 4
    21 0 2 6 5 2
    22 1 2 7 8 2
    23 0 3 6 5 7
    24 0 0 3 17 4
    25 0 5 8 8 3
    26 0 1 8 6 6
    27 0 0 3 10 7
    28 0 0 1 6 12
    29 0 0 5 5 10
    30 0 0 2 8 7
    31 0 0 2 13 15
    32 0 0 0 3 9
    33 0 0 0 4 24
    34 0 0 0 5 27
    35 0 0 0 0 17



  2. #2
    Registered User
    Join Date
    04-14-2007
    Location
    Phoenix AZ
    Posts
    32
    Anybody have any ideas for this, this seems to be the most relevant clue for lotto prediction. I tried some of the count funtion, but I may have done them wrong.

  3. #3
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by farmerTom
    I want to be able to count the number of cells until a number is found.

    The number range is 1 -35 and there are 5 columns. I want to be able to search a column $B1 until it finds the number 1, then return the number of rows that the number "skipped" in another column. I would like the function to loop until 1 - 35 are checked and updated.
    My search field would be a lottery number history.... 5 columns. My return field would be 5 columns and 35 rows returning how many rows were skipped for each number in each column. Like this, but this is a count of how many times the number occured per column.

    ~~

    Hi,

    you could try a helper column (say I) with, in I1

    =IF(B1=1,1,"")

    and in I2, formula fill to the end of your data,

    =IF(B2<1,"",IF(B2=1,1,IF(I1<>"",I1+1,"")))

    and select the =Max of that column.

    repeat a column for each number required.

    hth
    ---
    Si fractum non sit, noli id reficere.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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