Counting Consecutive Repeated Values...

Lewiy

Well-known Member

Joined

Jan 5, 2007

Messages

4,284

• Oct 18, 2007

• #2

Do you mean something like this?:

Excel Workbook
	A	B
1	Number	Consecutive zeros
2
3	1	0
4	3	0
5	5	0
6	0	1
7	0	2
8	0	3
9	0	4
10	0	5
11	5	0
12	6	0
13	4	0
14	0	1
15	0	2
16	0	3
Sheet1

Formula is copied down the column.

 

Upvote 0

N

nicksonic

New Member

Joined

Oct 17, 2007

Messages

41

• Oct 18, 2007

• #3

Thanks for the reply. That's close, but the data goes from left to right, i.e. a1 to j1 for example, so the formula would go in k1...

[Nick]

 

Upvote 0

Lewiy

Well-known Member

Joined

Jan 5, 2007

Messages

4,284

• Oct 18, 2007

• #4

Are you just looking for one formula which counts the largest number of consecutive zeros that occur in the row?

 

Upvote 0

N

nicksonic

New Member

Joined

Oct 17, 2007

Messages

41

• Oct 18, 2007

• #5

hi lewiy. yes, initially, although in an ideal world i'd like a formula that counts if the number of consecutive zeroes is 4 or not as well as both pieces of info would be useful.

Thanks,

Nick

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Oct 18, 2007

• #6

Assuming you don't have blanks in the range A1:J1 try this formula

=MAX(FREQUENCY(IF(A1:J1=0,COLUMN(A1:J1)),IF(A1:J1<>0,COLUMN(A1:J1))))

needs to be confirmed with CTRL+SHIFT+ENTER

 

Upvote 0

Lewiy

Well-known Member

Joined

Jan 5, 2007

Messages

4,284

• Oct 18, 2007

• #7

Something more like this then?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Number	1	0	0	0	0	1	0	1	2	3
2	Consecutive Zeros		1	2	3	4	0	1	0	0	0
3
4	Max Consecutive zeros		4
5	Are there four?		TRUE
Sheet1

 

Upvote 0

N

nicksonic

New Member

Joined

Oct 17, 2007

Messages

41

• Oct 18, 2007

• #8

these look great, i'll check them out and get back to you, thanks VERY MUCH for your help

[Nick]

 

Upvote 0

N

nicksonic

New Member

Joined

Oct 17, 2007

Messages

41

• Oct 19, 2007

• #9

Success! Thanks Barry, your formula worked perfectly - i didn't say that my non-positive data was a range of values and the formula didn't seem to like this, so i changed these values to 1's and it worked.

Thanks very much for your help

[Nick]

 

Upvote 0