countif question

a row as below:
1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6

I need to count all the continuous 1's from the left. Only the 1st (from left) 4 1's need to be counted; the 9th 1 should not be counted. Here the counting results should be 4.

I have several hundred rows like this. Please help to suggest a formula.

Thanks.

Please provide a little more info...........

Is all the data in one cell separated by commas like you show, or is each
number really in their own cell spanning multi columns?
Might the string of 1's start somewhere else in the string other than with
the first character?
Do the rows all contain 11 numbers like your sample?......If not, what is
the maximum?


Vaya con Dios,
Chuck, CABGx3



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

I can't claim much credit for this, but I took two of Harlan Grove's
ingenious solutions and put them together:

=1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1

entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
refers to the range being evaluated.

--

Vasant



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Wow, I didn't realize what an old thread this was. In this situation, you
should really start a new thread.

Also, I assumed that if the first column did not contain a 1, the result
should be 0 (your explanation was not very clear). It's more complicated if
you want to count the leftmost continuous sequence of 1s, regardless of
which column it starts in.

--

Vasant




"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
>I can't claim much credit for this, but I took two of Harlan Grove's
>ingenious solutions and put them together:
>
> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>
> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
> refers to the range being evaluated.
>
> --
>
> Vasant
>
>
>
> "yeedao" <[email protected]> wrote in
> message news:[email protected]...
>>
>> a row as below:
>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>
>> I need to count all the continuous 1's from the left. Only the 1st
>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>> Here the counting results should be 4.
>>
>> I have several hundred rows like this. Please help to suggest a
>> formula.
>>
>> Thanks.
>>
>>
>> --
>> yeedao
>> ------------------------------------------------------------------------
>> yeedao's Profile:
>> http://www.excelforum.com/member.php...o&userid=26268
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=395619
>>
>
>

Thanks for the help and sorry for not making the problem statement clearer.

Any number continuously from the left equal to 1 or greater than 1 is a saturation point; The goal is to count how many points are saturated;

If a number to its left is less than 1 and the number itself is greater than 1, this number is not counted as saturation but as noise.

Example:
1.001, 1.011, 0.98, 0.99,0.8, ..., Count =2; total 20 numbers;
0.99, 08,1.002, 1.003, 0.87, ...., Count =0; total 20 numbers;
1.012, 0.98,0.78,1.02,0.809,...., Count = 1; total 20 numbers;

Each number is in a cell; Each row has 20 numbers;

I thought I can use 1 as an example but it does cause confusion to others and to myself. It is not a integer, it can be 1.001, 1.011, 1.0002, ....;

I am not sure if that equation still works.

Thanks again.

It's not an old thread Vasant, it's the way Outlook Express does it
other newsreaders won't attach a thread with the same subject to an old
thread

--
Regards,

Peo Sjoblom

(No private emails please)


"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
> Wow, I didn't realize what an old thread this was. In this situation, you
> should really start a new thread.
>
> Also, I assumed that if the first column did not contain a 1, the result
> should be 0 (your explanation was not very clear). It's more complicated
> if you want to count the leftmost continuous sequence of 1s, regardless of
> which column it starts in.
>
> --
>
> Vasant
>
>
>
>
> "Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
> news:[email protected]...
>>I can't claim much credit for this, but I took two of Harlan Grove's
>>ingenious solutions and put them together:
>>
>> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>>
>> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
>> refers to the range being evaluated.
>>
>> --
>>
>> Vasant
>>
>>
>>
>> "yeedao" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> a row as below:
>>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>>
>>> I need to count all the continuous 1's from the left. Only the 1st
>>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>>> Here the counting results should be 4.
>>>
>>> I have several hundred rows like this. Please help to suggest a
>>> formula.
>>>
>>> Thanks.
>>>
>>>
>>> --
>>> yeedao
>>> ------------------------------------------------------------------------
>>> yeedao's Profile:
>>> http://www.excelforum.com/member.php...o&userid=26268
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=395619
>>>
>>
>>
>
>

Assuming that Columns A through T contain your data...

If there will always be at least one number less than 1, try...

=MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,0,COLUMN(A1:T1)-COLUMN(A1)))<1,0)-1

...confirmed with CONTROL+SHIFT+ENTER, otherwise try...

=IF(COUNTIF(A1:T1,">=1")<>COLUMNS(A1:T1),MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,0,COLUMN(A1:T1)-COLUMN(A1)))<1,0)-1,COLUMNS(A1:T1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Originally Posted by yeedao

Thanks for the help and sorry for not making the problem statement clearer.

Any number continuously from the left equal to 1 or greater than 1 is a saturation point; The goal is to count how many points are saturated;

If a number to its left is less than 1 and the number itself is greater than 1, this number is not counted as saturation but as noise.

Example:
1.001, 1.011, 0.98, 0.99,0.8, ..., Count =2; total 20 numbers;
0.99, 08,1.002, 1.003, 0.87, ...., Count =0; total 20 numbers;
1.012, 0.98,0.78,1.02,0.809,...., Count = 1; total 20 numbers;

Each number is in a cell; Each row has 20 numbers;

I thought I can use 1 as an example but it does cause confusion to others and to myself. It is not a integer, it can be 1.001, 1.011, 1.0002, ....;

I am not sure if that equation still works.

Thanks again.

I don't have time to test right now but replacing "<>" with "<" should work.

--

Vasant


"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the help and sorry for not making the problem statement
> clearer.
>
> Any number continuously from the left equal to 1 or greater than 1 is a
> saturation point; The goal is to count how many points are saturated;
>
> If a number to its left is less than 1 and the number itself is greater
> than 1, this number is not counted as saturation but as noise.
>
> Example:
> 1.001, 1.011, 0.98, 0.99,0.8, ..., Count =2; total 20 numbers;
> 0.99, 08,1.002, 1.003, 0.87, ...., Count =0; total 20 numbers;
> 1.012, 0.98,0.78,1.02,0.809,...., Count = 1; total 20 numbers;
>
> Each number is in a cell; Each row has 20 numbers;
>
> I thought I can use 1 as an example but it does cause confusion to
> others and to myself. It is not a integer, it can be 1.001, 1.011,
> 1.0002, ....;
>
> I am not sure if that equation still works.
>
> Thanks again.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Thanks.
there will always a number less than 1 in a row and I did try the fomula
"=MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,0,COLUMN(A1:T1)-COLUMN(A1)))<1,0)-1"
and it did worked for a while, but is not stable. Sounds strange?

I tried it this morning, it all provided the right output. But after I was trying to retype the fomula, it does not work anymore. Then I re-copied the fomular and it still does not work. Always came out as "#N/A".

The other fomular did not really work and results in "#N/A".

Two more questions:
1. if I need to count numbers greater than 1.2, shall I change the fomular into below?
"=MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,0,COLUMN(A1:T1)-COLUMN(A1)))<1.2,0)-1"

2. What do you guys mean by "...confirmed with CONTROL+SHIFT+ENTER"?

Thanks very much.

Originally Posted by yeedao

1. if I need to count numbers greater than 1.2, shall I change the fomular into below?
"=MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,0,COLUMN(A1:T1)-COLUMN(A1)))<1.2,0)-1"

Yes.

2. What do you guys mean by "...confirmed with CONTROL+SHIFT+ENTER"?

After typing the formula, instead of hitting just the ENTER, press the CONTROL and SHIFT keys down, then while holding them down, hit the ENTER key. Excel will place braces around the formula which will indicate that you've entered the formula correctly.

Thanks a lot, Domenic.
Everything is now working great.

Really really appreciate it.

Yeedao

Please provide a little more info...........

Is all the data in one cell separated by commas like you show, or is each
number really in their own cell spanning multi columns?
Might the string of 1's start somewhere else in the string other than with
the first character?
Do the rows all contain 11 numbers like your sample?......If not, what is
the maximum?


Vaya con Dios,
Chuck, CABGx3



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

I can't claim much credit for this, but I took two of Harlan Grove's
ingenious solutions and put them together:

=1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1

entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
refers to the range being evaluated.

--

Vasant



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Wow, I didn't realize what an old thread this was. In this situation, you
should really start a new thread.

Also, I assumed that if the first column did not contain a 1, the result
should be 0 (your explanation was not very clear). It's more complicated if
you want to count the leftmost continuous sequence of 1s, regardless of
which column it starts in.

--

Vasant




"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
>I can't claim much credit for this, but I took two of Harlan Grove's
>ingenious solutions and put them together:
>
> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>
> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
> refers to the range being evaluated.
>
> --
>
> Vasant
>
>
>
> "yeedao" <[email protected]> wrote in
> message news:[email protected]...
>>
>> a row as below:
>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>
>> I need to count all the continuous 1's from the left. Only the 1st
>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>> Here the counting results should be 4.
>>
>> I have several hundred rows like this. Please help to suggest a
>> formula.
>>
>> Thanks.
>>
>>
>> --
>> yeedao
>> ------------------------------------------------------------------------
>> yeedao's Profile:
>> http://www.excelforum.com/member.php...o&userid=26268
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=395619
>>
>
>

It's not an old thread Vasant, it's the way Outlook Express does it
other newsreaders won't attach a thread with the same subject to an old
thread

--
Regards,

Peo Sjoblom

(No private emails please)


"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
> Wow, I didn't realize what an old thread this was. In this situation, you
> should really start a new thread.
>
> Also, I assumed that if the first column did not contain a 1, the result
> should be 0 (your explanation was not very clear). It's more complicated
> if you want to count the leftmost continuous sequence of 1s, regardless of
> which column it starts in.
>
> --
>
> Vasant
>
>
>
>
> "Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
> news:[email protected]...
>>I can't claim much credit for this, but I took two of Harlan Grove's
>>ingenious solutions and put them together:
>>
>> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>>
>> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
>> refers to the range being evaluated.
>>
>> --
>>
>> Vasant
>>
>>
>>
>> "yeedao" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> a row as below:
>>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>>
>>> I need to count all the continuous 1's from the left. Only the 1st
>>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>>> Here the counting results should be 4.
>>>
>>> I have several hundred rows like this. Please help to suggest a
>>> formula.
>>>
>>> Thanks.
>>>
>>>
>>> --
>>> yeedao
>>> ------------------------------------------------------------------------
>>> yeedao's Profile:
>>> http://www.excelforum.com/member.php...o&userid=26268
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=395619
>>>
>>
>>
>
>

I don't have time to test right now but replacing "<>" with "<" should work.

--

Vasant


"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the help and sorry for not making the problem statement
> clearer.
>
> Any number continuously from the left equal to 1 or greater than 1 is a
> saturation point; The goal is to count how many points are saturated;
>
> If a number to its left is less than 1 and the number itself is greater
> than 1, this number is not counted as saturation but as noise.
>
> Example:
> 1.001, 1.011, 0.98, 0.99,0.8, ..., Count =2; total 20 numbers;
> 0.99, 08,1.002, 1.003, 0.87, ...., Count =0; total 20 numbers;
> 1.012, 0.98,0.78,1.02,0.809,...., Count = 1; total 20 numbers;
>
> Each number is in a cell; Each row has 20 numbers;
>
> I thought I can use 1 as an example but it does cause confusion to
> others and to myself. It is not a integer, it can be 1.001, 1.011,
> 1.0002, ....;
>
> I am not sure if that equation still works.
>
> Thanks again.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Please provide a little more info...........

Is all the data in one cell separated by commas like you show, or is each
number really in their own cell spanning multi columns?
Might the string of 1's start somewhere else in the string other than with
the first character?
Do the rows all contain 11 numbers like your sample?......If not, what is
the maximum?


Vaya con Dios,
Chuck, CABGx3



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

I can't claim much credit for this, but I took two of Harlan Grove's
ingenious solutions and put them together:

=1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1

entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
refers to the range being evaluated.

--

Vasant



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Wow, I didn't realize what an old thread this was. In this situation, you
should really start a new thread.

Also, I assumed that if the first column did not contain a 1, the result
should be 0 (your explanation was not very clear). It's more complicated if
you want to count the leftmost continuous sequence of 1s, regardless of
which column it starts in.

--

Vasant




"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
>I can't claim much credit for this, but I took two of Harlan Grove's
>ingenious solutions and put them together:
>
> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>
> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
> refers to the range being evaluated.
>
> --
>
> Vasant
>
>
>
> "yeedao" <[email protected]> wrote in
> message news:[email protected]...
>>
>> a row as below:
>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>
>> I need to count all the continuous 1's from the left. Only the 1st
>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>> Here the counting results should be 4.
>>
>> I have several hundred rows like this. Please help to suggest a
>> formula.
>>
>> Thanks.
>>
>>
>> --
>> yeedao
>> ------------------------------------------------------------------------
>> yeedao's Profile:
>> http://www.excelforum.com/member.php...o&userid=26268
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=395619
>>
>
>

It's not an old thread Vasant, it's the way Outlook Express does it
other newsreaders won't attach a thread with the same subject to an old
thread

--
Regards,

Peo Sjoblom

(No private emails please)


"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
> Wow, I didn't realize what an old thread this was. In this situation, you
> should really start a new thread.
>
> Also, I assumed that if the first column did not contain a 1, the result
> should be 0 (your explanation was not very clear). It's more complicated
> if you want to count the leftmost continuous sequence of 1s, regardless of
> which column it starts in.
>
> --
>
> Vasant
>
>
>
>
> "Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
> news:[email protected]...
>>I can't claim much credit for this, but I took two of Harlan Grove's
>>ingenious solutions and put them together:
>>
>> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>>
>> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
>> refers to the range being evaluated.
>>
>> --
>>
>> Vasant
>>
>>
>>
>> "yeedao" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> a row as below:
>>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>>
>>> I need to count all the continuous 1's from the left. Only the 1st
>>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>>> Here the counting results should be 4.
>>>
>>> I have several hundred rows like this. Please help to suggest a
>>> formula.
>>>
>>> Thanks.
>>>
>>>
>>> --
>>> yeedao
>>> ------------------------------------------------------------------------
>>> yeedao's Profile:
>>> http://www.excelforum.com/member.php...o&userid=26268
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=395619
>>>
>>
>>
>
>

I don't have time to test right now but replacing "<>" with "<" should work.

--

Vasant


"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the help and sorry for not making the problem statement
> clearer.
>
> Any number continuously from the left equal to 1 or greater than 1 is a
> saturation point; The goal is to count how many points are saturated;
>
> If a number to its left is less than 1 and the number itself is greater
> than 1, this number is not counted as saturation but as noise.
>
> Example:
> 1.001, 1.011, 0.98, 0.99,0.8, ..., Count =2; total 20 numbers;
> 0.99, 08,1.002, 1.003, 0.87, ...., Count =0; total 20 numbers;
> 1.012, 0.98,0.78,1.02,0.809,...., Count = 1; total 20 numbers;
>
> Each number is in a cell; Each row has 20 numbers;
>
> I thought I can use 1 as an example but it does cause confusion to
> others and to myself. It is not a integer, it can be 1.001, 1.011,
> 1.0002, ....;
>
> I am not sure if that equation still works.
>
> Thanks again.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Please provide a little more info...........

Is all the data in one cell separated by commas like you show, or is each
number really in their own cell spanning multi columns?
Might the string of 1's start somewhere else in the string other than with
the first character?
Do the rows all contain 11 numbers like your sample?......If not, what is
the maximum?


Vaya con Dios,
Chuck, CABGx3



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

I can't claim much credit for this, but I took two of Harlan Grove's
ingenious solutions and put them together:

=1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1

entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
refers to the range being evaluated.

--

Vasant



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Wow, I didn't realize what an old thread this was. In this situation, you
should really start a new thread.

Also, I assumed that if the first column did not contain a 1, the result
should be 0 (your explanation was not very clear). It's more complicated if
you want to count the leftmost continuous sequence of 1s, regardless of
which column it starts in.

--

Vasant




"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
>I can't claim much credit for this, but I took two of Harlan Grove's
>ingenious solutions and put them together:
>
> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>
> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
> refers to the range being evaluated.
>
> --
>
> Vasant
>
>
>
> "yeedao" <[email protected]> wrote in
> message news:[email protected]...
>>
>> a row as below:
>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>
>> I need to count all the continuous 1's from the left. Only the 1st
>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>> Here the counting results should be 4.
>>
>> I have several hundred rows like this. Please help to suggest a
>> formula.
>>
>> Thanks.
>>
>>
>> --
>> yeedao
>> ------------------------------------------------------------------------
>> yeedao's Profile:
>> http://www.excelforum.com/member.php...o&userid=26268
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=395619
>>
>
>

It's not an old thread Vasant, it's the way Outlook Express does it
other newsreaders won't attach a thread with the same subject to an old
thread

--
Regards,

Peo Sjoblom

(No private emails please)


"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
> Wow, I didn't realize what an old thread this was. In this situation, you
> should really start a new thread.
>
> Also, I assumed that if the first column did not contain a 1, the result
> should be 0 (your explanation was not very clear). It's more complicated
> if you want to count the leftmost continuous sequence of 1s, regardless of
> which column it starts in.
>
> --
>
> Vasant
>
>
>
>
> "Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
> news:[email protected]...
>>I can't claim much credit for this, but I took two of Harlan Grove's
>>ingenious solutions and put them together:
>>
>> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>>
>> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
>> refers to the range being evaluated.
>>
>> --
>>
>> Vasant
>>
>>
>>
>> "yeedao" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> a row as below:
>>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>>
>>> I need to count all the continuous 1's from the left. Only the 1st
>>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>>> Here the counting results should be 4.
>>>
>>> I have several hundred rows like this. Please help to suggest a
>>> formula.
>>>
>>> Thanks.
>>>
>>>
>>> --
>>> yeedao
>>> ------------------------------------------------------------------------
>>> yeedao's Profile:
>>> http://www.excelforum.com/member.php...o&userid=26268
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=395619
>>>
>>
>>
>
>

I don't have time to test right now but replacing "<>" with "<" should work.

--

Vasant


"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the help and sorry for not making the problem statement
> clearer.
>
> Any number continuously from the left equal to 1 or greater than 1 is a
> saturation point; The goal is to count how many points are saturated;
>
> If a number to its left is less than 1 and the number itself is greater
> than 1, this number is not counted as saturation but as noise.
>
> Example:
> 1.001, 1.011, 0.98, 0.99,0.8, ..., Count =2; total 20 numbers;
> 0.99, 08,1.002, 1.003, 0.87, ...., Count =0; total 20 numbers;
> 1.012, 0.98,0.78,1.02,0.809,...., Count = 1; total 20 numbers;
>
> Each number is in a cell; Each row has 20 numbers;
>
> I thought I can use 1 as an example but it does cause confusion to
> others and to myself. It is not a integer, it can be 1.001, 1.011,
> 1.0002, ....;
>
> I am not sure if that equation still works.
>
> Thanks again.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Please provide a little more info...........

Is all the data in one cell separated by commas like you show, or is each
number really in their own cell spanning multi columns?
Might the string of 1's start somewhere else in the string other than with
the first character?
Do the rows all contain 11 numbers like your sample?......If not, what is
the maximum?


Vaya con Dios,
Chuck, CABGx3



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

I can't claim much credit for this, but I took two of Harlan Grove's
ingenious solutions and put them together:

=1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1

entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
refers to the range being evaluated.

--

Vasant



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Wow, I didn't realize what an old thread this was. In this situation, you
should really start a new thread.

Also, I assumed that if the first column did not contain a 1, the result
should be 0 (your explanation was not very clear). It's more complicated if
you want to count the leftmost continuous sequence of 1s, regardless of
which column it starts in.

--

Vasant




"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
>I can't claim much credit for this, but I took two of Harlan Grove's
>ingenious solutions and put them together:
>
> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>
> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
> refers to the range being evaluated.
>
> --
>
> Vasant
>
>
>
> "yeedao" <[email protected]> wrote in
> message news:[email protected]...
>>
>> a row as below:
>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>
>> I need to count all the continuous 1's from the left. Only the 1st
>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>> Here the counting results should be 4.
>>
>> I have several hundred rows like this. Please help to suggest a
>> formula.
>>
>> Thanks.
>>
>>
>> --
>> yeedao
>> ------------------------------------------------------------------------
>> yeedao's Profile:
>> http://www.excelforum.com/member.php...o&userid=26268
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=395619
>>
>
>

It's not an old thread Vasant, it's the way Outlook Express does it
other newsreaders won't attach a thread with the same subject to an old
thread

--
Regards,

Peo Sjoblom

(No private emails please)


"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
> Wow, I didn't realize what an old thread this was. In this situation, you
> should really start a new thread.
>
> Also, I assumed that if the first column did not contain a 1, the result
> should be 0 (your explanation was not very clear). It's more complicated
> if you want to count the leftmost continuous sequence of 1s, regardless of
> which column it starts in.
>
> --
>
> Vasant
>
>
>
>
> "Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
> news:[email protected]...
>>I can't claim much credit for this, but I took two of Harlan Grove's
>>ingenious solutions and put them together:
>>
>> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>>
>> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
>> refers to the range being evaluated.
>>
>> --
>>
>> Vasant
>>
>>
>>
>> "yeedao" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> a row as below:
>>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>>
>>> I need to count all the continuous 1's from the left. Only the 1st
>>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>>> Here the counting results should be 4.
>>>
>>> I have several hundred rows like this. Please help to suggest a
>>> formula.
>>>
>>> Thanks.
>>>
>>>
>>> --
>>> yeedao
>>> ------------------------------------------------------------------------
>>> yeedao's Profile:
>>> http://www.excelforum.com/member.php...o&userid=26268
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=395619
>>>
>>
>>
>
>

I don't have time to test right now but replacing "<>" with "<" should work.

--

Vasant


"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the help and sorry for not making the problem statement
> clearer.
>
> Any number continuously from the left equal to 1 or greater than 1 is a
> saturation point; The goal is to count how many points are saturated;
>
> If a number to its left is less than 1 and the number itself is greater
> than 1, this number is not counted as saturation but as noise.
>
> Example:
> 1.001, 1.011, 0.98, 0.99,0.8, ..., Count =2; total 20 numbers;
> 0.99, 08,1.002, 1.003, 0.87, ...., Count =0; total 20 numbers;
> 1.012, 0.98,0.78,1.02,0.809,...., Count = 1; total 20 numbers;
>
> Each number is in a cell; Each row has 20 numbers;
>
> I thought I can use 1 as an example but it does cause confusion to
> others and to myself. It is not a integer, it can be 1.001, 1.011,
> 1.0002, ....;
>
> I am not sure if that equation still works.
>
> Thanks again.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Please provide a little more info...........

Is all the data in one cell separated by commas like you show, or is each
number really in their own cell spanning multi columns?
Might the string of 1's start somewhere else in the string other than with
the first character?
Do the rows all contain 11 numbers like your sample?......If not, what is
the maximum?


Vaya con Dios,
Chuck, CABGx3



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

I can't claim much credit for this, but I took two of Harlan Grove's
ingenious solutions and put them together:

=1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1

entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
refers to the range being evaluated.

--

Vasant



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Wow, I didn't realize what an old thread this was. In this situation, you
should really start a new thread.

Also, I assumed that if the first column did not contain a 1, the result
should be 0 (your explanation was not very clear). It's more complicated if
you want to count the leftmost continuous sequence of 1s, regardless of
which column it starts in.

--

Vasant




"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
>I can't claim much credit for this, but I took two of Harlan Grove's
>ingenious solutions and put them together:
>
> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>
> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
> refers to the range being evaluated.
>
> --
>
> Vasant
>
>
>
> "yeedao" <[email protected]> wrote in
> message news:[email protected]...
>>
>> a row as below:
>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>
>> I need to count all the continuous 1's from the left. Only the 1st
>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>> Here the counting results should be 4.
>>
>> I have several hundred rows like this. Please help to suggest a
>> formula.
>>
>> Thanks.
>>
>>
>> --
>> yeedao
>> ------------------------------------------------------------------------
>> yeedao's Profile:
>> http://www.excelforum.com/member.php...o&userid=26268
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=395619
>>
>
>

It's not an old thread Vasant, it's the way Outlook Express does it
other newsreaders won't attach a thread with the same subject to an old
thread

--
Regards,

Peo Sjoblom

(No private emails please)


"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
> Wow, I didn't realize what an old thread this was. In this situation, you
> should really start a new thread.
>
> Also, I assumed that if the first column did not contain a 1, the result
> should be 0 (your explanation was not very clear). It's more complicated
> if you want to count the leftmost continuous sequence of 1s, regardless of
> which column it starts in.
>
> --
>
> Vasant
>
>
>
>
> "Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
> news:[email protected]...
>>I can't claim much credit for this, but I took two of Harlan Grove's
>>ingenious solutions and put them together:
>>
>> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>>
>> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
>> refers to the range being evaluated.
>>
>> --
>>
>> Vasant
>>
>>
>>
>> "yeedao" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> a row as below:
>>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>>
>>> I need to count all the continuous 1's from the left. Only the 1st
>>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>>> Here the counting results should be 4.
>>>
>>> I have several hundred rows like this. Please help to suggest a
>>> formula.
>>>
>>> Thanks.
>>>
>>>
>>> --
>>> yeedao
>>> ------------------------------------------------------------------------
>>> yeedao's Profile:
>>> http://www.excelforum.com/member.php...o&userid=26268
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=395619
>>>
>>
>>
>
>

I don't have time to test right now but replacing "<>" with "<" should work.

--

Vasant


"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the help and sorry for not making the problem statement
> clearer.
>
> Any number continuously from the left equal to 1 or greater than 1 is a
> saturation point; The goal is to count how many points are saturated;
>
> If a number to its left is less than 1 and the number itself is greater
> than 1, this number is not counted as saturation but as noise.
>
> Example:
> 1.001, 1.011, 0.98, 0.99,0.8, ..., Count =2; total 20 numbers;
> 0.99, 08,1.002, 1.003, 0.87, ...., Count =0; total 20 numbers;
> 1.012, 0.98,0.78,1.02,0.809,...., Count = 1; total 20 numbers;
>
> Each number is in a cell; Each row has 20 numbers;
>
> I thought I can use 1 as an example but it does cause confusion to
> others and to myself. It is not a integer, it can be 1.001, 1.011,
> 1.0002, ....;
>
> I am not sure if that equation still works.
>
> Thanks again.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Please provide a little more info...........

Is all the data in one cell separated by commas like you show, or is each
number really in their own cell spanning multi columns?
Might the string of 1's start somewhere else in the string other than with
the first character?
Do the rows all contain 11 numbers like your sample?......If not, what is
the maximum?


Vaya con Dios,
Chuck, CABGx3



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

I can't claim much credit for this, but I took two of Harlan Grove's
ingenious solutions and put them together:

=1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1

entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
refers to the range being evaluated.

--

Vasant



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Wow, I didn't realize what an old thread this was. In this situation, you
should really start a new thread.

Also, I assumed that if the first column did not contain a 1, the result
should be 0 (your explanation was not very clear). It's more complicated if
you want to count the leftmost continuous sequence of 1s, regardless of
which column it starts in.

--

Vasant




"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
>I can't claim much credit for this, but I took two of Harlan Grove's
>ingenious solutions and put them together:
>
> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>
> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
> refers to the range being evaluated.
>
> --
>
> Vasant
>
>
>
> "yeedao" <[email protected]> wrote in
> message news:[email protected]...
>>
>> a row as below:
>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>
>> I need to count all the continuous 1's from the left. Only the 1st
>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>> Here the counting results should be 4.
>>
>> I have several hundred rows like this. Please help to suggest a
>> formula.
>>
>> Thanks.
>>
>>
>> --
>> yeedao
>> ------------------------------------------------------------------------
>> yeedao's Profile:
>> http://www.excelforum.com/member.php...o&userid=26268
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=395619
>>
>
>

It's not an old thread Vasant, it's the way Outlook Express does it
other newsreaders won't attach a thread with the same subject to an old
thread

--
Regards,

Peo Sjoblom

(No private emails please)


"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
> Wow, I didn't realize what an old thread this was. In this situation, you
> should really start a new thread.
>
> Also, I assumed that if the first column did not contain a 1, the result
> should be 0 (your explanation was not very clear). It's more complicated
> if you want to count the leftmost continuous sequence of 1s, regardless of
> which column it starts in.
>
> --
>
> Vasant
>
>
>
>
> "Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
> news:[email protected]...
>>I can't claim much credit for this, but I took two of Harlan Grove's
>>ingenious solutions and put them together:
>>
>> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>>
>> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
>> refers to the range being evaluated.
>>
>> --
>>
>> Vasant
>>
>>
>>
>> "yeedao" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> a row as below:
>>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>>
>>> I need to count all the continuous 1's from the left. Only the 1st
>>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>>> Here the counting results should be 4.
>>>
>>> I have several hundred rows like this. Please help to suggest a
>>> formula.
>>>
>>> Thanks.
>>>
>>>
>>> --
>>> yeedao
>>> ------------------------------------------------------------------------
>>> yeedao's Profile:
>>> http://www.excelforum.com/member.php...o&userid=26268
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=395619
>>>
>>
>>
>
>

I don't have time to test right now but replacing "<>" with "<" should work.

--

Vasant


"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the help and sorry for not making the problem statement
> clearer.
>
> Any number continuously from the left equal to 1 or greater than 1 is a
> saturation point; The goal is to count how many points are saturated;
>
> If a number to its left is less than 1 and the number itself is greater
> than 1, this number is not counted as saturation but as noise.
>
> Example:
> 1.001, 1.011, 0.98, 0.99,0.8, ..., Count =2; total 20 numbers;
> 0.99, 08,1.002, 1.003, 0.87, ...., Count =0; total 20 numbers;
> 1.012, 0.98,0.78,1.02,0.809,...., Count = 1; total 20 numbers;
>
> Each number is in a cell; Each row has 20 numbers;
>
> I thought I can use 1 as an example but it does cause confusion to
> others and to myself. It is not a integer, it can be 1.001, 1.011,
> 1.0002, ....;
>
> I am not sure if that equation still works.
>
> Thanks again.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Please provide a little more info...........

Is all the data in one cell separated by commas like you show, or is each
number really in their own cell spanning multi columns?
Might the string of 1's start somewhere else in the string other than with
the first character?
Do the rows all contain 11 numbers like your sample?......If not, what is
the maximum?


Vaya con Dios,
Chuck, CABGx3



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

I can't claim much credit for this, but I took two of Harlan Grove's
ingenious solutions and put them together:

=1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1

entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
refers to the range being evaluated.

--

Vasant



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Wow, I didn't realize what an old thread this was. In this situation, you
should really start a new thread.

Also, I assumed that if the first column did not contain a 1, the result
should be 0 (your explanation was not very clear). It's more complicated if
you want to count the leftmost continuous sequence of 1s, regardless of
which column it starts in.

--

Vasant




"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
>I can't claim much credit for this, but I took two of Harlan Grove's
>ingenious solutions and put them together:
>
> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>
> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
> refers to the range being evaluated.
>
> --
>
> Vasant
>
>
>
> "yeedao" <[email protected]> wrote in
> message news:[email protected]...
>>
>> a row as below:
>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>
>> I need to count all the continuous 1's from the left. Only the 1st
>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>> Here the counting results should be 4.
>>
>> I have several hundred rows like this. Please help to suggest a
>> formula.
>>
>> Thanks.
>>
>>
>> --
>> yeedao
>> ------------------------------------------------------------------------
>> yeedao's Profile:
>> http://www.excelforum.com/member.php...o&userid=26268
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=395619
>>
>
>

It's not an old thread Vasant, it's the way Outlook Express does it
other newsreaders won't attach a thread with the same subject to an old
thread

--
Regards,

Peo Sjoblom

(No private emails please)


"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
> Wow, I didn't realize what an old thread this was. In this situation, you
> should really start a new thread.
>
> Also, I assumed that if the first column did not contain a 1, the result
> should be 0 (your explanation was not very clear). It's more complicated
> if you want to count the leftmost continuous sequence of 1s, regardless of
> which column it starts in.
>
> --
>
> Vasant
>
>
>
>
> "Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
> news:[email protected]...
>>I can't claim much credit for this, but I took two of Harlan Grove's
>>ingenious solutions and put them together:
>>
>> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>>
>> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
>> refers to the range being evaluated.
>>
>> --
>>
>> Vasant
>>
>>
>>
>> "yeedao" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> a row as below:
>>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>>
>>> I need to count all the continuous 1's from the left. Only the 1st
>>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>>> Here the counting results should be 4.
>>>
>>> I have several hundred rows like this. Please help to suggest a
>>> formula.
>>>
>>> Thanks.
>>>
>>>
>>> --
>>> yeedao
>>> ------------------------------------------------------------------------
>>> yeedao's Profile:
>>> http://www.excelforum.com/member.php...o&userid=26268
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=395619
>>>
>>
>>
>
>

I don't have time to test right now but replacing "<>" with "<" should work.

--

Vasant


"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the help and sorry for not making the problem statement
> clearer.
>
> Any number continuously from the left equal to 1 or greater than 1 is a
> saturation point; The goal is to count how many points are saturated;
>
> If a number to its left is less than 1 and the number itself is greater
> than 1, this number is not counted as saturation but as noise.
>
> Example:
> 1.001, 1.011, 0.98, 0.99,0.8, ..., Count =2; total 20 numbers;
> 0.99, 08,1.002, 1.003, 0.87, ...., Count =0; total 20 numbers;
> 1.012, 0.98,0.78,1.02,0.809,...., Count = 1; total 20 numbers;
>
> Each number is in a cell; Each row has 20 numbers;
>
> I thought I can use 1 as an example but it does cause confusion to
> others and to myself. It is not a integer, it can be 1.001, 1.011,
> 1.0002, ....;
>
> I am not sure if that equation still works.
>
> Thanks again.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Please provide a little more info...........

Is all the data in one cell separated by commas like you show, or is each
number really in their own cell spanning multi columns?
Might the string of 1's start somewhere else in the string other than with
the first character?
Do the rows all contain 11 numbers like your sample?......If not, what is
the maximum?


Vaya con Dios,
Chuck, CABGx3



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

I can't claim much credit for this, but I took two of Harlan Grove's
ingenious solutions and put them together:

=1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1

entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
refers to the range being evaluated.

--

Vasant



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Wow, I didn't realize what an old thread this was. In this situation, you
should really start a new thread.

Also, I assumed that if the first column did not contain a 1, the result
should be 0 (your explanation was not very clear). It's more complicated if
you want to count the leftmost continuous sequence of 1s, regardless of
which column it starts in.

--

Vasant




"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
>I can't claim much credit for this, but I took two of Harlan Grove's
>ingenious solutions and put them together:
>
> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>
> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
> refers to the range being evaluated.
>
> --
>
> Vasant
>
>
>
> "yeedao" <[email protected]> wrote in
> message news:[email protected]...
>>
>> a row as below:
>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>
>> I need to count all the continuous 1's from the left. Only the 1st
>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>> Here the counting results should be 4.
>>
>> I have several hundred rows like this. Please help to suggest a
>> formula.
>>
>> Thanks.
>>
>>
>> --
>> yeedao
>> ------------------------------------------------------------------------
>> yeedao's Profile:
>> http://www.excelforum.com/member.php...o&userid=26268
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=395619
>>
>
>

It's not an old thread Vasant, it's the way Outlook Express does it
other newsreaders won't attach a thread with the same subject to an old
thread

--
Regards,

Peo Sjoblom

(No private emails please)


"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
> Wow, I didn't realize what an old thread this was. In this situation, you
> should really start a new thread.
>
> Also, I assumed that if the first column did not contain a 1, the result
> should be 0 (your explanation was not very clear). It's more complicated
> if you want to count the leftmost continuous sequence of 1s, regardless of
> which column it starts in.
>
> --
>
> Vasant
>
>
>
>
> "Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
> news:[email protected]...
>>I can't claim much credit for this, but I took two of Harlan Grove's
>>ingenious solutions and put them together:
>>
>> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>>
>> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
>> refers to the range being evaluated.
>>
>> --
>>
>> Vasant
>>
>>
>>
>> "yeedao" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> a row as below:
>>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>>
>>> I need to count all the continuous 1's from the left. Only the 1st
>>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>>> Here the counting results should be 4.
>>>
>>> I have several hundred rows like this. Please help to suggest a
>>> formula.
>>>
>>> Thanks.
>>>
>>>
>>> --
>>> yeedao
>>> ------------------------------------------------------------------------
>>> yeedao's Profile:
>>> http://www.excelforum.com/member.php...o&userid=26268
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=395619
>>>
>>
>>
>
>

I don't have time to test right now but replacing "<>" with "<" should work.

--

Vasant


"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the help and sorry for not making the problem statement
> clearer.
>
> Any number continuously from the left equal to 1 or greater than 1 is a
> saturation point; The goal is to count how many points are saturated;
>
> If a number to its left is less than 1 and the number itself is greater
> than 1, this number is not counted as saturation but as noise.
>
> Example:
> 1.001, 1.011, 0.98, 0.99,0.8, ..., Count =2; total 20 numbers;
> 0.99, 08,1.002, 1.003, 0.87, ...., Count =0; total 20 numbers;
> 1.012, 0.98,0.78,1.02,0.809,...., Count = 1; total 20 numbers;
>
> Each number is in a cell; Each row has 20 numbers;
>
> I thought I can use 1 as an example but it does cause confusion to
> others and to myself. It is not a integer, it can be 1.001, 1.011,
> 1.0002, ....;
>
> I am not sure if that equation still works.
>
> Thanks again.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Please provide a little more info...........

Is all the data in one cell separated by commas like you show, or is each
number really in their own cell spanning multi columns?
Might the string of 1's start somewhere else in the string other than with
the first character?
Do the rows all contain 11 numbers like your sample?......If not, what is
the maximum?


Vaya con Dios,
Chuck, CABGx3



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

I can't claim much credit for this, but I took two of Harlan Grove's
ingenious solutions and put them together:

=1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1

entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
refers to the range being evaluated.

--

Vasant



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Wow, I didn't realize what an old thread this was. In this situation, you
should really start a new thread.

Also, I assumed that if the first column did not contain a 1, the result
should be 0 (your explanation was not very clear). It's more complicated if
you want to count the leftmost continuous sequence of 1s, regardless of
which column it starts in.

--

Vasant




"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
>I can't claim much credit for this, but I took two of Harlan Grove's
>ingenious solutions and put them together:
>
> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>
> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
> refers to the range being evaluated.
>
> --
>
> Vasant
>
>
>
> "yeedao" <[email protected]> wrote in
> message news:[email protected]...
>>
>> a row as below:
>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>
>> I need to count all the continuous 1's from the left. Only the 1st
>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>> Here the counting results should be 4.
>>
>> I have several hundred rows like this. Please help to suggest a
>> formula.
>>
>> Thanks.
>>
>>
>> --
>> yeedao
>> ------------------------------------------------------------------------
>> yeedao's Profile:
>> http://www.excelforum.com/member.php...o&userid=26268
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=395619
>>
>
>

It's not an old thread Vasant, it's the way Outlook Express does it
other newsreaders won't attach a thread with the same subject to an old
thread

--
Regards,

Peo Sjoblom

(No private emails please)


"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
> Wow, I didn't realize what an old thread this was. In this situation, you
> should really start a new thread.
>
> Also, I assumed that if the first column did not contain a 1, the result
> should be 0 (your explanation was not very clear). It's more complicated
> if you want to count the leftmost continuous sequence of 1s, regardless of
> which column it starts in.
>
> --
>
> Vasant
>
>
>
>
> "Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
> news:[email protected]...
>>I can't claim much credit for this, but I took two of Harlan Grove's
>>ingenious solutions and put them together:
>>
>> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>>
>> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
>> refers to the range being evaluated.
>>
>> --
>>
>> Vasant
>>
>>
>>
>> "yeedao" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> a row as below:
>>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>>
>>> I need to count all the continuous 1's from the left. Only the 1st
>>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>>> Here the counting results should be 4.
>>>
>>> I have several hundred rows like this. Please help to suggest a
>>> formula.
>>>
>>> Thanks.
>>>
>>>
>>> --
>>> yeedao
>>> ------------------------------------------------------------------------
>>> yeedao's Profile:
>>> http://www.excelforum.com/member.php...o&userid=26268
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=395619
>>>
>>
>>
>
>

I don't have time to test right now but replacing "<>" with "<" should work.

--

Vasant


"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the help and sorry for not making the problem statement
> clearer.
>
> Any number continuously from the left equal to 1 or greater than 1 is a
> saturation point; The goal is to count how many points are saturated;
>
> If a number to its left is less than 1 and the number itself is greater
> than 1, this number is not counted as saturation but as noise.
>
> Example:
> 1.001, 1.011, 0.98, 0.99,0.8, ..., Count =2; total 20 numbers;
> 0.99, 08,1.002, 1.003, 0.87, ...., Count =0; total 20 numbers;
> 1.012, 0.98,0.78,1.02,0.809,...., Count = 1; total 20 numbers;
>
> Each number is in a cell; Each row has 20 numbers;
>
> I thought I can use 1 as an example but it does cause confusion to
> others and to myself. It is not a integer, it can be 1.001, 1.011,
> 1.0002, ....;
>
> I am not sure if that equation still works.
>
> Thanks again.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Please provide a little more info...........

Is all the data in one cell separated by commas like you show, or is each
number really in their own cell spanning multi columns?
Might the string of 1's start somewhere else in the string other than with
the first character?
Do the rows all contain 11 numbers like your sample?......If not, what is
the maximum?


Vaya con Dios,
Chuck, CABGx3



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

I can't claim much credit for this, but I took two of Harlan Grove's
ingenious solutions and put them together:

=1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1

entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
refers to the range being evaluated.

--

Vasant



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Wow, I didn't realize what an old thread this was. In this situation, you
should really start a new thread.

Also, I assumed that if the first column did not contain a 1, the result
should be 0 (your explanation was not very clear). It's more complicated if
you want to count the leftmost continuous sequence of 1s, regardless of
which column it starts in.

--

Vasant




"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
>I can't claim much credit for this, but I took two of Harlan Grove's
>ingenious solutions and put them together:
>
> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>
> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
> refers to the range being evaluated.
>
> --
>
> Vasant
>
>
>
> "yeedao" <[email protected]> wrote in
> message news:[email protected]...
>>
>> a row as below:
>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>
>> I need to count all the continuous 1's from the left. Only the 1st
>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>> Here the counting results should be 4.
>>
>> I have several hundred rows like this. Please help to suggest a
>> formula.
>>
>> Thanks.
>>
>>
>> --
>> yeedao
>> ------------------------------------------------------------------------
>> yeedao's Profile:
>> http://www.excelforum.com/member.php...o&userid=26268
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=395619
>>
>
>

It's not an old thread Vasant, it's the way Outlook Express does it
other newsreaders won't attach a thread with the same subject to an old
thread

--
Regards,

Peo Sjoblom

(No private emails please)


"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
> Wow, I didn't realize what an old thread this was. In this situation, you
> should really start a new thread.
>
> Also, I assumed that if the first column did not contain a 1, the result
> should be 0 (your explanation was not very clear). It's more complicated
> if you want to count the leftmost continuous sequence of 1s, regardless of
> which column it starts in.
>
> --
>
> Vasant
>
>
>
>
> "Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
> news:[email protected]...
>>I can't claim much credit for this, but I took two of Harlan Grove's
>>ingenious solutions and put them together:
>>
>> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>>
>> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
>> refers to the range being evaluated.
>>
>> --
>>
>> Vasant
>>
>>
>>
>> "yeedao" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> a row as below:
>>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>>
>>> I need to count all the continuous 1's from the left. Only the 1st
>>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>>> Here the counting results should be 4.
>>>
>>> I have several hundred rows like this. Please help to suggest a
>>> formula.
>>>
>>> Thanks.
>>>
>>>
>>> --
>>> yeedao
>>> ------------------------------------------------------------------------
>>> yeedao's Profile:
>>> http://www.excelforum.com/member.php...o&userid=26268
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=395619
>>>
>>
>>
>
>

I don't have time to test right now but replacing "<>" with "<" should work.

--

Vasant


"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the help and sorry for not making the problem statement
> clearer.
>
> Any number continuously from the left equal to 1 or greater than 1 is a
> saturation point; The goal is to count how many points are saturated;
>
> If a number to its left is less than 1 and the number itself is greater
> than 1, this number is not counted as saturation but as noise.
>
> Example:
> 1.001, 1.011, 0.98, 0.99,0.8, ..., Count =2; total 20 numbers;
> 0.99, 08,1.002, 1.003, 0.87, ...., Count =0; total 20 numbers;
> 1.012, 0.98,0.78,1.02,0.809,...., Count = 1; total 20 numbers;
>
> Each number is in a cell; Each row has 20 numbers;
>
> I thought I can use 1 as an example but it does cause confusion to
> others and to myself. It is not a integer, it can be 1.001, 1.011,
> 1.0002, ....;
>
> I am not sure if that equation still works.
>
> Thanks again.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Please provide a little more info...........

Is all the data in one cell separated by commas like you show, or is each
number really in their own cell spanning multi columns?
Might the string of 1's start somewhere else in the string other than with
the first character?
Do the rows all contain 11 numbers like your sample?......If not, what is
the maximum?


Vaya con Dios,
Chuck, CABGx3



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

I can't claim much credit for this, but I took two of Harlan Grove's
ingenious solutions and put them together:

=1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1

entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
refers to the range being evaluated.

--

Vasant



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Wow, I didn't realize what an old thread this was. In this situation, you
should really start a new thread.

Also, I assumed that if the first column did not contain a 1, the result
should be 0 (your explanation was not very clear). It's more complicated if
you want to count the leftmost continuous sequence of 1s, regardless of
which column it starts in.

--

Vasant




"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
>I can't claim much credit for this, but I took two of Harlan Grove's
>ingenious solutions and put them together:
>
> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>
> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
> refers to the range being evaluated.
>
> --
>
> Vasant
>
>
>
> "yeedao" <[email protected]> wrote in
> message news:[email protected]...
>>
>> a row as below:
>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>
>> I need to count all the continuous 1's from the left. Only the 1st
>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>> Here the counting results should be 4.
>>
>> I have several hundred rows like this. Please help to suggest a
>> formula.
>>
>> Thanks.
>>
>>
>> --
>> yeedao
>> ------------------------------------------------------------------------
>> yeedao's Profile:
>> http://www.excelforum.com/member.php...o&userid=26268
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=395619
>>
>
>

It's not an old thread Vasant, it's the way Outlook Express does it
other newsreaders won't attach a thread with the same subject to an old
thread

--
Regards,

Peo Sjoblom

(No private emails please)


"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
> Wow, I didn't realize what an old thread this was. In this situation, you
> should really start a new thread.
>
> Also, I assumed that if the first column did not contain a 1, the result
> should be 0 (your explanation was not very clear). It's more complicated
> if you want to count the leftmost continuous sequence of 1s, regardless of
> which column it starts in.
>
> --
>
> Vasant
>
>
>
>
> "Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
> news:[email protected]...
>>I can't claim much credit for this, but I took two of Harlan Grove's
>>ingenious solutions and put them together:
>>
>> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>>
>> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
>> refers to the range being evaluated.
>>
>> --
>>
>> Vasant
>>
>>
>>
>> "yeedao" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> a row as below:
>>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>>
>>> I need to count all the continuous 1's from the left. Only the 1st
>>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>>> Here the counting results should be 4.
>>>
>>> I have several hundred rows like this. Please help to suggest a
>>> formula.
>>>
>>> Thanks.
>>>
>>>
>>> --
>>> yeedao
>>> ------------------------------------------------------------------------
>>> yeedao's Profile:
>>> http://www.excelforum.com/member.php...o&userid=26268
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=395619
>>>
>>
>>
>
>

I don't have time to test right now but replacing "<>" with "<" should work.

--

Vasant


"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the help and sorry for not making the problem statement
> clearer.
>
> Any number continuously from the left equal to 1 or greater than 1 is a
> saturation point; The goal is to count how many points are saturated;
>
> If a number to its left is less than 1 and the number itself is greater
> than 1, this number is not counted as saturation but as noise.
>
> Example:
> 1.001, 1.011, 0.98, 0.99,0.8, ..., Count =2; total 20 numbers;
> 0.99, 08,1.002, 1.003, 0.87, ...., Count =0; total 20 numbers;
> 1.012, 0.98,0.78,1.02,0.809,...., Count = 1; total 20 numbers;
>
> Each number is in a cell; Each row has 20 numbers;
>
> I thought I can use 1 as an example but it does cause confusion to
> others and to myself. It is not a integer, it can be 1.001, 1.011,
> 1.0002, ....;
>
> I am not sure if that equation still works.
>
> Thanks again.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Please provide a little more info...........

Is all the data in one cell separated by commas like you show, or is each
number really in their own cell spanning multi columns?
Might the string of 1's start somewhere else in the string other than with
the first character?
Do the rows all contain 11 numbers like your sample?......If not, what is
the maximum?


Vaya con Dios,
Chuck, CABGx3



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

I can't claim much credit for this, but I took two of Harlan Grove's
ingenious solutions and put them together:

=1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1

entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
refers to the range being evaluated.

--

Vasant



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Wow, I didn't realize what an old thread this was. In this situation, you
should really start a new thread.

Also, I assumed that if the first column did not contain a 1, the result
should be 0 (your explanation was not very clear). It's more complicated if
you want to count the leftmost continuous sequence of 1s, regardless of
which column it starts in.

--

Vasant




"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
>I can't claim much credit for this, but I took two of Harlan Grove's
>ingenious solutions and put them together:
>
> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>
> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
> refers to the range being evaluated.
>
> --
>
> Vasant
>
>
>
> "yeedao" <[email protected]> wrote in
> message news:[email protected]...
>>
>> a row as below:
>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>
>> I need to count all the continuous 1's from the left. Only the 1st
>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>> Here the counting results should be 4.
>>
>> I have several hundred rows like this. Please help to suggest a
>> formula.
>>
>> Thanks.
>>
>>
>> --
>> yeedao
>> ------------------------------------------------------------------------
>> yeedao's Profile:
>> http://www.excelforum.com/member.php...o&userid=26268
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=395619
>>
>
>

It's not an old thread Vasant, it's the way Outlook Express does it
other newsreaders won't attach a thread with the same subject to an old
thread

--
Regards,

Peo Sjoblom

(No private emails please)


"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
> Wow, I didn't realize what an old thread this was. In this situation, you
> should really start a new thread.
>
> Also, I assumed that if the first column did not contain a 1, the result
> should be 0 (your explanation was not very clear). It's more complicated
> if you want to count the leftmost continuous sequence of 1s, regardless of
> which column it starts in.
>
> --
>
> Vasant
>
>
>
>
> "Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
> news:[email protected]...
>>I can't claim much credit for this, but I took two of Harlan Grove's
>>ingenious solutions and put them together:
>>
>> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>>
>> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
>> refers to the range being evaluated.
>>
>> --
>>
>> Vasant
>>
>>
>>
>> "yeedao" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> a row as below:
>>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>>
>>> I need to count all the continuous 1's from the left. Only the 1st
>>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>>> Here the counting results should be 4.
>>>
>>> I have several hundred rows like this. Please help to suggest a
>>> formula.
>>>
>>> Thanks.
>>>
>>>
>>> --
>>> yeedao
>>> ------------------------------------------------------------------------
>>> yeedao's Profile:
>>> http://www.excelforum.com/member.php...o&userid=26268
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=395619
>>>
>>
>>
>
>

I don't have time to test right now but replacing "<>" with "<" should work.

--

Vasant


"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the help and sorry for not making the problem statement
> clearer.
>
> Any number continuously from the left equal to 1 or greater than 1 is a
> saturation point; The goal is to count how many points are saturated;
>
> If a number to its left is less than 1 and the number itself is greater
> than 1, this number is not counted as saturation but as noise.
>
> Example:
> 1.001, 1.011, 0.98, 0.99,0.8, ..., Count =2; total 20 numbers;
> 0.99, 08,1.002, 1.003, 0.87, ...., Count =0; total 20 numbers;
> 1.012, 0.98,0.78,1.02,0.809,...., Count = 1; total 20 numbers;
>
> Each number is in a cell; Each row has 20 numbers;
>
> I thought I can use 1 as an example but it does cause confusion to
> others and to myself. It is not a integer, it can be 1.001, 1.011,
> 1.0002, ....;
>
> I am not sure if that equation still works.
>
> Thanks again.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Please provide a little more info...........

Is all the data in one cell separated by commas like you show, or is each
number really in their own cell spanning multi columns?
Might the string of 1's start somewhere else in the string other than with
the first character?
Do the rows all contain 11 numbers like your sample?......If not, what is
the maximum?


Vaya con Dios,
Chuck, CABGx3



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

I can't claim much credit for this, but I took two of Harlan Grove's
ingenious solutions and put them together:

=1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1

entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
refers to the range being evaluated.

--

Vasant



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Wow, I didn't realize what an old thread this was. In this situation, you
should really start a new thread.

Also, I assumed that if the first column did not contain a 1, the result
should be 0 (your explanation was not very clear). It's more complicated if
you want to count the leftmost continuous sequence of 1s, regardless of
which column it starts in.

--

Vasant




"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
>I can't claim much credit for this, but I took two of Harlan Grove's
>ingenious solutions and put them together:
>
> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>
> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
> refers to the range being evaluated.
>
> --
>
> Vasant
>
>
>
> "yeedao" <[email protected]> wrote in
> message news:[email protected]...
>>
>> a row as below:
>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>
>> I need to count all the continuous 1's from the left. Only the 1st
>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>> Here the counting results should be 4.
>>
>> I have several hundred rows like this. Please help to suggest a
>> formula.
>>
>> Thanks.
>>
>>
>> --
>> yeedao
>> ------------------------------------------------------------------------
>> yeedao's Profile:
>> http://www.excelforum.com/member.php...o&userid=26268
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=395619
>>
>
>

It's not an old thread Vasant, it's the way Outlook Express does it
other newsreaders won't attach a thread with the same subject to an old
thread

--
Regards,

Peo Sjoblom

(No private emails please)


"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
> Wow, I didn't realize what an old thread this was. In this situation, you
> should really start a new thread.
>
> Also, I assumed that if the first column did not contain a 1, the result
> should be 0 (your explanation was not very clear). It's more complicated
> if you want to count the leftmost continuous sequence of 1s, regardless of
> which column it starts in.
>
> --
>
> Vasant
>
>
>
>
> "Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
> news:[email protected]...
>>I can't claim much credit for this, but I took two of Harlan Grove's
>>ingenious solutions and put them together:
>>
>> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>>
>> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
>> refers to the range being evaluated.
>>
>> --
>>
>> Vasant
>>
>>
>>
>> "yeedao" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> a row as below:
>>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>>
>>> I need to count all the continuous 1's from the left. Only the 1st
>>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>>> Here the counting results should be 4.
>>>
>>> I have several hundred rows like this. Please help to suggest a
>>> formula.
>>>
>>> Thanks.
>>>
>>>
>>> --
>>> yeedao
>>> ------------------------------------------------------------------------
>>> yeedao's Profile:
>>> http://www.excelforum.com/member.php...o&userid=26268
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=395619
>>>
>>
>>
>
>

I don't have time to test right now but replacing "<>" with "<" should work.

--

Vasant


"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the help and sorry for not making the problem statement
> clearer.
>
> Any number continuously from the left equal to 1 or greater than 1 is a
> saturation point; The goal is to count how many points are saturated;
>
> If a number to its left is less than 1 and the number itself is greater
> than 1, this number is not counted as saturation but as noise.
>
> Example:
> 1.001, 1.011, 0.98, 0.99,0.8, ..., Count =2; total 20 numbers;
> 0.99, 08,1.002, 1.003, 0.87, ...., Count =0; total 20 numbers;
> 1.012, 0.98,0.78,1.02,0.809,...., Count = 1; total 20 numbers;
>
> Each number is in a cell; Each row has 20 numbers;
>
> I thought I can use 1 as an example but it does cause confusion to
> others and to myself. It is not a integer, it can be 1.001, 1.011,
> 1.0002, ....;
>
> I am not sure if that equation still works.
>
> Thanks again.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Please provide a little more info...........

Is all the data in one cell separated by commas like you show, or is each
number really in their own cell spanning multi columns?
Might the string of 1's start somewhere else in the string other than with
the first character?
Do the rows all contain 11 numbers like your sample?......If not, what is
the maximum?


Vaya con Dios,
Chuck, CABGx3



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

I can't claim much credit for this, but I took two of Harlan Grove's
ingenious solutions and put them together:

=1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1

entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
refers to the range being evaluated.

--

Vasant



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Wow, I didn't realize what an old thread this was. In this situation, you
should really start a new thread.

Also, I assumed that if the first column did not contain a 1, the result
should be 0 (your explanation was not very clear). It's more complicated if
you want to count the leftmost continuous sequence of 1s, regardless of
which column it starts in.

--

Vasant




"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
>I can't claim much credit for this, but I took two of Harlan Grove's
>ingenious solutions and put them together:
>
> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>
> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
> refers to the range being evaluated.
>
> --
>
> Vasant
>
>
>
> "yeedao" <[email protected]> wrote in
> message news:[email protected]...
>>
>> a row as below:
>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>
>> I need to count all the continuous 1's from the left. Only the 1st
>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>> Here the counting results should be 4.
>>
>> I have several hundred rows like this. Please help to suggest a
>> formula.
>>
>> Thanks.
>>
>>
>> --
>> yeedao
>> ------------------------------------------------------------------------
>> yeedao's Profile:
>> http://www.excelforum.com/member.php...o&userid=26268
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=395619
>>
>
>

It's not an old thread Vasant, it's the way Outlook Express does it
other newsreaders won't attach a thread with the same subject to an old
thread

--
Regards,

Peo Sjoblom

(No private emails please)


"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
> Wow, I didn't realize what an old thread this was. In this situation, you
> should really start a new thread.
>
> Also, I assumed that if the first column did not contain a 1, the result
> should be 0 (your explanation was not very clear). It's more complicated
> if you want to count the leftmost continuous sequence of 1s, regardless of
> which column it starts in.
>
> --
>
> Vasant
>
>
>
>
> "Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
> news:[email protected]...
>>I can't claim much credit for this, but I took two of Harlan Grove's
>>ingenious solutions and put them together:
>>
>> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>>
>> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
>> refers to the range being evaluated.
>>
>> --
>>
>> Vasant
>>
>>
>>
>> "yeedao" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> a row as below:
>>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>>
>>> I need to count all the continuous 1's from the left. Only the 1st
>>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>>> Here the counting results should be 4.
>>>
>>> I have several hundred rows like this. Please help to suggest a
>>> formula.
>>>
>>> Thanks.
>>>
>>>
>>> --
>>> yeedao
>>> ------------------------------------------------------------------------
>>> yeedao's Profile:
>>> http://www.excelforum.com/member.php...o&userid=26268
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=395619
>>>
>>
>>
>
>

I don't have time to test right now but replacing "<>" with "<" should work.

--

Vasant


"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the help and sorry for not making the problem statement
> clearer.
>
> Any number continuously from the left equal to 1 or greater than 1 is a
> saturation point; The goal is to count how many points are saturated;
>
> If a number to its left is less than 1 and the number itself is greater
> than 1, this number is not counted as saturation but as noise.
>
> Example:
> 1.001, 1.011, 0.98, 0.99,0.8, ..., Count =2; total 20 numbers;
> 0.99, 08,1.002, 1.003, 0.87, ...., Count =0; total 20 numbers;
> 1.012, 0.98,0.78,1.02,0.809,...., Count = 1; total 20 numbers;
>
> Each number is in a cell; Each row has 20 numbers;
>
> I thought I can use 1 as an example but it does cause confusion to
> others and to myself. It is not a integer, it can be 1.001, 1.011,
> 1.0002, ....;
>
> I am not sure if that equation still works.
>
> Thanks again.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Please provide a little more info...........

Is all the data in one cell separated by commas like you show, or is each
number really in their own cell spanning multi columns?
Might the string of 1's start somewhere else in the string other than with
the first character?
Do the rows all contain 11 numbers like your sample?......If not, what is
the maximum?


Vaya con Dios,
Chuck, CABGx3



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

I can't claim much credit for this, but I took two of Harlan Grove's
ingenious solutions and put them together:

=1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1

entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
refers to the range being evaluated.

--

Vasant



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Wow, I didn't realize what an old thread this was. In this situation, you
should really start a new thread.

Also, I assumed that if the first column did not contain a 1, the result
should be 0 (your explanation was not very clear). It's more complicated if
you want to count the leftmost continuous sequence of 1s, regardless of
which column it starts in.

--

Vasant




"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
>I can't claim much credit for this, but I took two of Harlan Grove's
>ingenious solutions and put them together:
>
> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>
> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
> refers to the range being evaluated.
>
> --
>
> Vasant
>
>
>
> "yeedao" <[email protected]> wrote in
> message news:[email protected]...
>>
>> a row as below:
>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>
>> I need to count all the continuous 1's from the left. Only the 1st
>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>> Here the counting results should be 4.
>>
>> I have several hundred rows like this. Please help to suggest a
>> formula.
>>
>> Thanks.
>>
>>
>> --
>> yeedao
>> ------------------------------------------------------------------------
>> yeedao's Profile:
>> http://www.excelforum.com/member.php...o&userid=26268
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=395619
>>
>
>

It's not an old thread Vasant, it's the way Outlook Express does it
other newsreaders won't attach a thread with the same subject to an old
thread

--
Regards,

Peo Sjoblom

(No private emails please)


"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
> Wow, I didn't realize what an old thread this was. In this situation, you
> should really start a new thread.
>
> Also, I assumed that if the first column did not contain a 1, the result
> should be 0 (your explanation was not very clear). It's more complicated
> if you want to count the leftmost continuous sequence of 1s, regardless of
> which column it starts in.
>
> --
>
> Vasant
>
>
>
>
> "Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
> news:[email protected]...
>>I can't claim much credit for this, but I took two of Harlan Grove's
>>ingenious solutions and put them together:
>>
>> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>>
>> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
>> refers to the range being evaluated.
>>
>> --
>>
>> Vasant
>>
>>
>>
>> "yeedao" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> a row as below:
>>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>>
>>> I need to count all the continuous 1's from the left. Only the 1st
>>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>>> Here the counting results should be 4.
>>>
>>> I have several hundred rows like this. Please help to suggest a
>>> formula.
>>>
>>> Thanks.
>>>
>>>
>>> --
>>> yeedao
>>> ------------------------------------------------------------------------
>>> yeedao's Profile:
>>> http://www.excelforum.com/member.php...o&userid=26268
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=395619
>>>
>>
>>
>
>

I don't have time to test right now but replacing "<>" with "<" should work.

--

Vasant


"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the help and sorry for not making the problem statement
> clearer.
>
> Any number continuously from the left equal to 1 or greater than 1 is a
> saturation point; The goal is to count how many points are saturated;
>
> If a number to its left is less than 1 and the number itself is greater
> than 1, this number is not counted as saturation but as noise.
>
> Example:
> 1.001, 1.011, 0.98, 0.99,0.8, ..., Count =2; total 20 numbers;
> 0.99, 08,1.002, 1.003, 0.87, ...., Count =0; total 20 numbers;
> 1.012, 0.98,0.78,1.02,0.809,...., Count = 1; total 20 numbers;
>
> Each number is in a cell; Each row has 20 numbers;
>
> I thought I can use 1 as an example but it does cause confusion to
> others and to myself. It is not a integer, it can be 1.001, 1.011,
> 1.0002, ....;
>
> I am not sure if that equation still works.
>
> Thanks again.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Please provide a little more info...........

Is all the data in one cell separated by commas like you show, or is each
number really in their own cell spanning multi columns?
Might the string of 1's start somewhere else in the string other than with
the first character?
Do the rows all contain 11 numbers like your sample?......If not, what is
the maximum?


Vaya con Dios,
Chuck, CABGx3



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

I can't claim much credit for this, but I took two of Harlan Grove's
ingenious solutions and put them together:

=1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1

entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
refers to the range being evaluated.

--

Vasant



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Wow, I didn't realize what an old thread this was. In this situation, you
should really start a new thread.

Also, I assumed that if the first column did not contain a 1, the result
should be 0 (your explanation was not very clear). It's more complicated if
you want to count the leftmost continuous sequence of 1s, regardless of
which column it starts in.

--

Vasant




"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
>I can't claim much credit for this, but I took two of Harlan Grove's
>ingenious solutions and put them together:
>
> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>
> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
> refers to the range being evaluated.
>
> --
>
> Vasant
>
>
>
> "yeedao" <[email protected]> wrote in
> message news:[email protected]...
>>
>> a row as below:
>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>
>> I need to count all the continuous 1's from the left. Only the 1st
>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>> Here the counting results should be 4.
>>
>> I have several hundred rows like this. Please help to suggest a
>> formula.
>>
>> Thanks.
>>
>>
>> --
>> yeedao
>> ------------------------------------------------------------------------
>> yeedao's Profile:
>> http://www.excelforum.com/member.php...o&userid=26268
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=395619
>>
>
>

It's not an old thread Vasant, it's the way Outlook Express does it
other newsreaders won't attach a thread with the same subject to an old
thread

--
Regards,

Peo Sjoblom

(No private emails please)


"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
> Wow, I didn't realize what an old thread this was. In this situation, you
> should really start a new thread.
>
> Also, I assumed that if the first column did not contain a 1, the result
> should be 0 (your explanation was not very clear). It's more complicated
> if you want to count the leftmost continuous sequence of 1s, regardless of
> which column it starts in.
>
> --
>
> Vasant
>
>
>
>
> "Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
> news:[email protected]...
>>I can't claim much credit for this, but I took two of Harlan Grove's
>>ingenious solutions and put them together:
>>
>> =1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1
>>
>> entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
>> refers to the range being evaluated.
>>
>> --
>>
>> Vasant
>>
>>
>>
>> "yeedao" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> a row as below:
>>> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>>>
>>> I need to count all the continuous 1's from the left. Only the 1st
>>> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
>>> Here the counting results should be 4.
>>>
>>> I have several hundred rows like this. Please help to suggest a
>>> formula.
>>>
>>> Thanks.
>>>
>>>
>>> --
>>> yeedao
>>> ------------------------------------------------------------------------
>>> yeedao's Profile:
>>> http://www.excelforum.com/member.php...o&userid=26268
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=395619
>>>
>>
>>
>
>

I don't have time to test right now but replacing "<>" with "<" should work.

--

Vasant


"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the help and sorry for not making the problem statement
> clearer.
>
> Any number continuously from the left equal to 1 or greater than 1 is a
> saturation point; The goal is to count how many points are saturated;
>
> If a number to its left is less than 1 and the number itself is greater
> than 1, this number is not counted as saturation but as noise.
>
> Example:
> 1.001, 1.011, 0.98, 0.99,0.8, ..., Count =2; total 20 numbers;
> 0.99, 08,1.002, 1.003, 0.87, ...., Count =0; total 20 numbers;
> 1.012, 0.98,0.78,1.02,0.809,...., Count = 1; total 20 numbers;
>
> Each number is in a cell; Each row has 20 numbers;
>
> I thought I can use 1 as an example but it does cause confusion to
> others and to myself. It is not a integer, it can be 1.001, 1.011,
> 1.0002, ....;
>
> I am not sure if that equation still works.
>
> Thanks again.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

Please provide a little more info...........

Is all the data in one cell separated by commas like you show, or is each
number really in their own cell spanning multi columns?
Might the string of 1's start somewhere else in the string other than with
the first character?
Do the rows all contain 11 numbers like your sample?......If not, what is
the maximum?


Vaya con Dios,
Chuck, CABGx3



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>

I can't claim much credit for this, but I took two of Harlan Grove's
ingenious solutions and put them together:

=1/MAX(IF(record<>1,1/TRANSPOSE(ROW(INDIRECT("1:"&COUNT(record)))),0))-1

entered as an array formula with <Ctrl> <Shift> <Enter>, where "record"
refers to the range being evaluated.

--

Vasant



"yeedao" <[email protected]> wrote in
message news:[email protected]...
>
> a row as below:
> 1, 1, 1, 1, 0.8, 0.9, 0.7, 0.5, 1, 0.8,0.6
>
> I need to count all the continuous 1's from the left. Only the 1st
> (from left) 4 1's need to be counted; the 9th 1 should not be counted.
> Here the counting results should be 4.
>
> I have several hundred rows like this. Please help to suggest a
> formula.
>
> Thanks.
>
>
> --
> yeedao
> ------------------------------------------------------------------------
> yeedao's Profile:
> http://www.excelforum.com/member.php...o&userid=26268
> View this thread: http://www.excelforum.com/showthread...hreadid=395619
>