countif question

Y

yeedao

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.
 
C

CLR

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
 
V

Vasant Nanavati

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.
 
V

Vasant Nanavati

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.
 
P

Peo Sjoblom

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)
 
Y

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.
 
V

Vasant Nanavati

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

Domenic

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!
 
Y

yeedao

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.
 
D

Domenic

yeedao said:
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"?
Click to expand...

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.
 
Y

yeedao

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

Really really appreciate it.

Yeeda
 
Top