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

**patrickfshield** · 11-05-2012, 12:24 PM

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

**Ace_XL** · 11-05-2012, 12:44 PM

perhaps with a helper column..

see attached

**patrickfshield** · 11-05-2012, 12:49 PM

helper column?

**Ace_XL** · 11-05-2012, 01:21 PM

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?

**patrickfshield** · 11-05-2012, 01:30 PM

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

**patrickfshield** · 11-05-2012, 01:36 PM

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

**patrickfshield** · 11-05-2012, 01:36 PM

i see it now

**patrickfshield** · 11-06-2012, 12:30 AM

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

**Ace_XL** · 11-06-2012, 01:32 AM

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

See attached..

**patrickfshield** · 11-06-2012, 02:48 AM

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

**JosephP** · 11-06-2012, 04:45 AM

this version is without helper columns for interest

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

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions