+ Reply to Thread
Results 1 to 11 of 11

Counting Number of Times a Value is Repeated REPEATEDLY (Back to Back)

  1. #1
    Registered User
    Join Date
    11-05-2012
    Location
    Tulsa, OK
    MS-Off Ver
    Excel 2007, 2010
    Posts
    13

    Counting Number of Times a Value is Repeated REPEATEDLY (Back to Back)

    Hello Excelforum Community,
    I've got a list of values, Yes and No, and I am trying to figure out a way to count how many times yes/no appears 2x, 3x,... 5x back to back. The only formulas i've found only count the total number of times a particular value appears. I'm wanting a formula that will count how many times a value appeared 2x, 3x, 4x, 5x.... etc. I included a mock up spreadsheet and colored coded it to help visually show what i'm am trying to accomplish.

    Also, is there a technical term for what i am trying to do?

    Thanks
    -patron

    1,000,000 Schrute Bucks to whoever can help me
    Attached Files Attached Files
    Last edited by patrickfshield; 11-13-2012 at 07:42 AM.

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    Re: Counting Number of Times a Value is Repeated REPEATEDLY (Back to Back)

    perhaps with a helper column..

    see attached
    Attached Files Attached Files
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    11-05-2012
    Location
    Tulsa, OK
    MS-Off Ver
    Excel 2007, 2010
    Posts
    13

    Re: Counting Number of Times a Value is Repeated REPEATEDLY (Back to Back)

    helper column?

  4. #4
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    Re: Counting Number of Times a Value is Repeated REPEATEDLY (Back to Back)

    Column B of the attachment in Post # 2 is the helper column which only populates number of consecutive yes/no at the last found instance (else it'll be blank)

    Based on this column, results are populated in the grid D3:E6

    Does this help?

  5. #5
    Registered User
    Join Date
    11-05-2012
    Location
    Tulsa, OK
    MS-Off Ver
    Excel 2007, 2010
    Posts
    13

    Re: Counting Number of Times a Value is Repeated REPEATEDLY (Back to Back)

    just an update on what i'm trying to accomplish. I've got a list of 10,000 yes/no entries, and I want to know...
    How many times 'Yes' appeared twice in a row
    How many times 'Yes' appeared 3x in a row
    How many times 'Yes' appeared 4x in a row
    How many times 'Yes' appeared ...x in a row
    How many times 'Yes' appeared 10x in a row

    thanks to those helping

  6. #6
    Registered User
    Join Date
    11-05-2012
    Location
    Tulsa, OK
    MS-Off Ver
    Excel 2007, 2010
    Posts
    13

    Re: Counting Number of Times a Value is Repeated REPEATEDLY (Back to Back)

    thanks ace, for some reason the spreadsheet wasn't loading the helper column.

  7. #7
    Registered User
    Join Date
    11-05-2012
    Location
    Tulsa, OK
    MS-Off Ver
    Excel 2007, 2010
    Posts
    13

    Re: Counting Number of Times a Value is Repeated REPEATEDLY (Back to Back)

    i see it now

  8. #8
    Registered User
    Join Date
    11-05-2012
    Location
    Tulsa, OK
    MS-Off Ver
    Excel 2007, 2010
    Posts
    13

    Re: Counting Number of Times a Value is Repeated REPEATEDLY (Back to Back)

    I applied helper column to my spreadsheet and for the most part it works properly except for 2 areas. First, when the first 2 outcomes are not the same, the helper column does populate correctly. Also when I mess around with the first 3 outcomes "win loss win, win loss loss, or loss win loss" it gives me a Circular Reference Warning. idk what that means.

    Second, populating the results of the helper column to grid F3:G16 are fine until it reaches 10 or more. Populating results for values 10 or more are inconsistent. I'm not sure where the results are coming from.

    I've highlighted the problem areas. Also to make the outcome change between win/loss numbers<13=Loss and numbers≥13=win

    Thanks
    -patron
    Attached Files Attached Files

  9. #9
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    Re: Counting Number of Times a Value is Repeated REPEATEDLY (Back to Back)

    Ive changed the cell references to remove the cicular reference and also updated the grid.

    See attached..
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    11-05-2012
    Location
    Tulsa, OK
    MS-Off Ver
    Excel 2007, 2010
    Posts
    13

    Re: Counting Number of Times a Value is Repeated REPEATEDLY (Back to Back)

    ace, thank you good sir, i will transfer 1,000,000 Schrute Bucks from my swedish bank account to ya.

  11. #11
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Counting Number of Times a Value is Repeated REPEATEDLY (Back to Back)

    this version is without helper columns for interest

    also, please don't forget to mark the thread solved
    Attached Files Attached Files
    Josie

    if at first you don't succeed try doing it the way your wife told you to

+ 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