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

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

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

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

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" <(E-Mail Removed)> wrote in message
news:6B8E4AAD-0D26-4B33-95D8-(E-Mail Removed)...
> 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

What if I don't have Excel 2007?

Am I SOL?

--
Biff
Microsoft Excel MVP


"Herbert Seidenberg" <herbds7-(E-Mail Removed)> wrote in message
news:2b7ad7f9-1c68-4cea-93f8-(E-Mail Removed)...
> Excel 2007
> No helper columns needed:
> http://www.mediafire.com/file/g2htngnzhjt/03_04_09.xlsx

>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" <(E-Mail Removed)> wrote in message
news:43B19E61-8441-4253-A79F-(E-Mail Removed)...
> 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

Hi,

Try this

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"stuart" <(E-Mail Removed)> wrote in message
news:6B8E4AAD-0D26-4B33-95D8-(E-Mail Removed)...
> 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

>=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?

--
Biff
Microsoft Excel MVP


"Ashish Mathur" <(E-Mail Removed)> wrote in message
news:76CB0EDD-65CF-4C4E-94AD-(E-Mail Removed)...
> Hi,
>
> Try this
>
> =SUMPRODUCT((D514=1)*(D514=D413))+1
>
> --
> Regards,
>
> Ashish Mathur
> Microsoft Excel MVP
> www.ashishmathur.com
>
> "stuart" <(E-Mail Removed)> wrote in message
> news:6B8E4AAD-0D26-4B33-95D8-(E-Mail Removed)...
>> 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
>

Thank you for pointing that out

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"T. Valko" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>>=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?
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Ashish Mathur" <(E-Mail Removed)> wrote in message
> news:76CB0EDD-65CF-4C4E-94AD-(E-Mail Removed)...
>> Hi,
>>
>> Try this
>>
>> =SUMPRODUCT((D514=1)*(D514=D413))+1
>>
>> --
>> Regards,
>>
>> Ashish Mathur
>> Microsoft Excel MVP
>> www.ashishmathur.com
>>
>> "stuart" <(E-Mail Removed)> wrote in message
>> news:6B8E4AAD-0D26-4B33-95D8-(E-Mail Removed)...
>>> 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
>>
>
>