Results 1 to 6 of 6

How to count consecutive same values in a range of cells

This is a discussion on How to count consecutive same values in a range of cells within the Excel Questions forums, part of the Question Forums category; I have 100 cells (4 columns by 25 rows) the contents of which are randomly generated to contain either a ...

  1. #1
    che
    che is offline
    New Member
    Join Date
    Jul 2004
    Posts
    4

    Default How to count consecutive same values in a range of cells

    I have 100 cells (4 columns by 25 rows) the contents of which are randomly generated to contain either a 1, 2 or 3. The cells are recalculated regularly. I am trying to determine/record the results of the number of times that 3 consecutive 1s, 2s or 3s appear, the number of times that 4 consecutive 1s, 2s or 3s appear....all the way to 20 consecutive appearances of 1s, 2s or 3s--to the extent such a run would be generated. I have tried like to come up with something and nothing seems to work. Also, because the numbers are recalculated, the results need to be tabulated/recorded in another cell before the cells are regenerated. If you can help, I would really appreciate it. Thanks a lot.

    CC

  2. #2
    Board Regular
    Join Date
    Nov 2003
    Posts
    960

    Default Re: How to count consecutive same values in a range of cells

    Consecutive across the row or down the columns? Does it count if the last two values in 1 row match the first 2 in the next? Etc...some clarifications could help.

  3. #3
    Board Regular
    Join Date
    May 2004
    Posts
    140

    Default Re: How to count consecutive same values in a range of cells

    ... just curious, what's the formula you're using to generate the random numbers? (excel's "random" numbers aren't very random...)

    Are you wanting to have the cells highlighted or just return a number for how many times you have a run of the same number?

    Does a 3 in row 25 of column A and a 3 in row 1 of column B count as a match?

    Thanks!!

    -nate

  4. #4
    che
    che is offline
    New Member
    Join Date
    Jul 2004
    Posts
    4

    Default Re: How to count consecutive same values in a range of cells

    Quote Originally Posted by martinee
    Consecutive across the row or down the columns? Does it count if the last two values in 1 row match the first 2 in the next? Etc...some clarifications could help.
    Sorry, consecutive down the columns and if the final cell in column 1 matches the first two cells in column 2 then it would be deemed to count. the numbers appear in cells d4:d28; g4:g28; i4:i28; l4:l28.

    thanks

  5. #5
    che
    che is offline
    New Member
    Join Date
    Jul 2004
    Posts
    4

    Default Re: How to count consecutive same values in a range of cells

    Quote Originally Posted by sky_lennard
    ... just curious, what's the formula you're using to generate the random numbers? (excel's "random" numbers aren't very random...)

    Are you wanting to have the cells highlighted or just return a number for how many times you have a run of the same number?

    Does a 3 in row 25 of column A and a 3 in row 1 of column B count as a match?

    Thanks!!

    -nate
    Nate-

    I recognize the shortcomings of excel's random number tools. I used randbetween.

    I don't need the cells highlighted, I just want to tabulate the number of times I have a run of the same number, with a run defined as at least 3 in a row.

    The answer to you last question is yes.

    Thanks for your help!

  6. #6
    che
    che is offline
    New Member
    Join Date
    Jul 2004
    Posts
    4

    Default Re: How to count consecutive same values in a range of cells

    Quote Originally Posted by sky_lennard
    ... just curious, what's the formula you're using to generate the random numbers? (excel's "random" numbers aren't very random...)

    Are you wanting to have the cells highlighted or just return a number for how many times you have a run of the same number?

    Does a 3 in row 25 of column A and a 3 in row 1 of column B count as a match?

    Thanks!!

    -nate
    Nate-

    I recognize the shortcomings of excel's random number tools. I used randbetween.

    I don't need the cells highlighted, I just want to tabulate the number of times I have a run of the same number, with a run defined as at least 3 in a row. Ideally, I would have a table off to the side that would have at the top as a heading 3, 4, 5, 6 ....20 and beneath each heading it would count the number of instances that such experience occurred.

    The answer to you last question is yes.

    Thanks for your help!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com