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

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

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

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

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

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.


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

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

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(TRU E,SUBTOTAL(9,OFFSET(A1,0,COLUMN(A1:T1)-COLUMN(A1)))<1,0)-1,COLUMNS(A1:T1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

yeedao Wrote:
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.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
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.


--
yeedao
------------------------------------------------------------------------
yeedao's Profile: http://www.excelforum.com/member.php...o&userid=26268
View this thread: http://www.excelforum.com/showthread...hreadid=395619

yeedao Wrote:
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.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=395619

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

Really really appreciate it.

Yeedao


--
yeedao
------------------------------------------------------------------------
yeedao's Profile: http://www.excelforum.com/member.php...o&userid=26268
View this thread: http://www.excelforum.com/showthread...hreadid=395619