count of max occcurances

[stuart]

hi everyone and all you super intelligent people out there
(thought id start with a compliment to make you smile)

anyway here is what im after from the below data i want to be able to see if
a person has more than 3 occurances of the number 1 in a row or not - i can
easily count the total 1's however i want to be able to see if more than 3
occur in sequence - any suggestions

column A
row person flag
1 1
2 0
3 0
4 1
5 1
6 1
7 1
8 0
9 0
10 1
="little formula to tell me yes 1 appeared it happend 4 times in row"

hope you can help

thanks in advance

me

 

[Gary''s Student]

In B1 enter:
=A1
In B2 enter:
=IF(A2=1,1+B1,0) and copy down
In C1 enter:
=MAX(B:B)

Here is an example:

0 0 5
0 0
0 0
0 0
1 1
0 0
0 0
1 1
1 2
1 3
1 4
1 5
0 0
0 0
0 0
0 0
0 0
1 1
0 0
0 0
1 1
1 2
1 3
0 0
0 0
1 1
1 2
1 3
1 4
0 0

Since C1 is 5, we know that there were 5 consecutive 1's somewhere in the
list.

 

[Shane Devenshire]

Hi,

You can use the following formula in B2 assuming the A1 and B1 contain the
column titles.

=(A2=1)*(A2+B1)

The MAX

 

[Herbert Seidenberg]

Excel 2007
No helper columns needed:
http://www.mediafire.com/file/g2htngnzhjt/03_04_09.xlsx

 

[T. Valko]

Try this array formula** :

=MAX(FREQUENCY(IF(A1:A10=1,ROW(A1:A10)),IF(A1:A10<>1,ROW(A1:A10))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[T. Valko]

What if I don't have Excel 2007?

Am I SOL?

 

[T. Valko]

A1 and B1 contain the column titles

=(A2=1)*(A2+B1)

If B1 is a column header (text) then A2+B1 will return a #VALUE! error.

 

[Ashish Mathur]

Hi,

Try this

=SUMPRODUCT((D514=1)*(D514=D413))+1

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

=SUMPRODUCT((D514=1)*(D514=D413))+1

That doesn't work. Try it with this data:

0;1;1;1;0;1;1;0;1;1

Also, what is the *actual* range in that formula? You're referencing 1 cell
above or 1 cell below the *actual* range. What if there's similar other
non-related data in those cells?

 

[Ashish Mathur]

Thank you for pointing that out

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com