count of max occcurances
 

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

count of max occcurances
 

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.
--
Gary''s Student - gsnu200836


"stuart" wrote:

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

count of max occcurances
 

Hi,

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

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

The MAX
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"stuart" wrote:

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

count of max occcurances
 

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

count of max occcurances
 

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.

--
Biff
Microsoft Excel MVP


"stuart" wrote in message
...
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

count of max occcurances
 

What if I don't have Excel 2007?

Am I SOL?

--
Biff
Microsoft Excel MVP


"Herbert Seidenberg" wrote in message
...
Excel 2007
No helper columns needed:
http://www.mediafire.com/file/g2htngnzhjt/03_04_09.xlsx

count of max occcurances
 

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.

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in message
...
Hi,

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

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

The MAX
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"stuart" wrote:

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

count of max occcurances
 

Hi,

Try this

=SUMPRODUCT((D5:D14=1)*(D5:D14=D4:D13))+1

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"stuart" wrote in message
...
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

count of max occcurances
 

=SUMPRODUCT((D5:D14=1)*(D5:D14=D4:D13))+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?

--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote in message
...
Hi,

Try this

=SUMPRODUCT((D5:D14=1)*(D5:D14=D4:D13))+1

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"stuart" wrote in message
...
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

count of max occcurances
 

Thank you for pointing that out

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"T. Valko" wrote in message
...
=SUMPRODUCT((D5:D14=1)*(D5:D14=D4:D13))+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?

--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote in message
...
Hi,

Try this

=SUMPRODUCT((D5:D14=1)*(D5:D14=D4:D13))+1

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"stuart" wrote in message
...
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