I have in column AU cell 39 to cell 500 random sequences of 1-10 rows ,
with numbers in them, (as an result of formulas)
I need to count the number of rows in each sequence that have numbers
of 35 or less , but only up to the first number of 35 or higher. eg:-

AU result
row 39 32 3
40 14
41 19
42 107
43 11
44 21
45 #value!
46 40
47 45
48 16
49 15
50 #value!
51 29 2
52 11
53 146
54 14
55 17
56 11
57 #value!
and so on down to 500 rows

all help is appreciated

thanks
bill gras

Replace "result" with 0 in AV38 and enter in AV39 & copy down:

=IF(OR(ISERROR(AU39),SUMIF(AU39,"35")35),
"",
IF(IF(ISERROR(AU40),1,AU4035),

COUNTIF(INDEX($AU$39:AU39,MATCH(9.99999999999999E+ 307,$AV$38:AV38)):AU39,"<=35"),
""))

bill gras wrote:
I have in column AU cell 39 to cell 500 random sequences of 1-10 rows ,
with numbers in them, (as an result of formulas)
I need to count the number of rows in each sequence that have numbers
of 35 or less , but only up to the first number of 35 or higher. eg:-

AU result
row 39 32 3
40 14
41 19
42 107
43 11
44 21
45 #value!
46 40
47 45
48 16
49 15
50 #value!
51 29 2
52 11
53 146
54 14
55 17
56 11
57 #value!
and so on down to 500 rows

all help is appreciated

thanks
bill gras

Hi Aladin,
Thank you for your time and your reply
There is one thing that your formula does not do is: count all numbers under
35 , stop at the first number larger than 35 and than go to the next sequence
, and count
all numbers under 35 stop at the first number larger than 35 and than go to
the next
sequence , and so on .
Can that be done ?
I apologise if I did not explained my self properly the first time .
as per your formula :-
-- AU
row 39 32
40 14
41 19 3
42 107
43 11
44 21 2
45 #value!
46 40
47 45
48 16
49 15 2
50 #value!
51 29
52 11 2
53 146
54 14
55 17
56 11 3
57 #value!
I hope you can still help me as I'm nearly finished my project
Thanking You
regards bill
bill gras


"Aladin Akyurek" wrote:

Replace "result" with 0 in AV38 and enter in AV39 & copy down:

=IF(OR(ISERROR(AU39),SUMIF(AU39,"35")35),
"",
IF(IF(ISERROR(AU40),1,AU4035),

COUNTIF(INDEX($AU$39:AU39,MATCH(9.99999999999999E+ 307,$AV$38:AV38)):AU39,"<=35"),
""))

bill gras wrote:
I have in column AU cell 39 to cell 500 random sequences of 1-10 rows ,
with numbers in them, (as an result of formulas)
I need to count the number of rows in each sequence that have numbers
of 35 or less , but only up to the first number of 35 or higher. eg:-

AU result
row 39 32 3
40 14
41 19
42 107
43 11
44 21
45 #value!
46 40
47 45
48 16
49 15
50 #value!
51 29 2
52 11
53 146
54 14
55 17
56 11
57 #value!
and so on down to 500 rows

all help is appreciated

thanks
bill gras

bill gras wrote:
Hi Aladin,
Thank you for your time and your reply
There is one thing that your formula does not do is: count all numbers under
35 , stop at the first number larger than 35 and than go to the next sequence
, and count
all numbers under 35 stop at the first number larger than 35 and than go to
the next
sequence , and so on .
Can that be done ?
I apologise if I did not explained my self properly the first time .
as per your formula :-
-- AU

row 39 32
40 14
41 19 3
42 107
43 11
44 21 2
45 #value!
46 40
[...]

Are you trying to say that 3 should appear at the same row as 107 and 2
at the same row as 40?

Hi Aladin
No . What I'm saying is that number 3 should be the only number (where it is)
and no number 2 because 107 is greater than 35 and that should be the cut off
point , there fore the next sequence should start after the #value! in
column AU45 . So the next sequence starts at AU 46 where the first number is
greater than 35 and that is the cut off point (no result) . The next sequence
starts at AU 51 after the #value! in AU50 and number 2 is correct , the cut
off point is at AU53 which the number is 146 so no number 3 . The next
sequence starts at AU58 after the #value! of AU57.

I hope this will help , please don't give up
thanks bill
--
bill gras


"Aladin Akyurek" wrote:



bill gras wrote:
Hi Aladin,
Thank you for your time and your reply
There is one thing that your formula does not do is: count all numbers under
35 , stop at the first number larger than 35 and than go to the next sequence
, and count
all numbers under 35 stop at the first number larger than 35 and than go to
the next
sequence , and so on .
Can that be done ?
I apologise if I did not explained my self properly the first time .
as per your formula :-
-- AU

row 39 32
40 14
41 19 3
42 107
43 11
44 21 2
45 #value!
46 40
[...]

Are you trying to say that 3 should appear at the same row as 107 and 2
at the same row as 40?

Let AU38 contain #VALUE!...

AV39, copied down:

=IF(1-ISNUMBER(AU38),MATCH(TRUE,INDEX(AU39:$AU$50035,0) ,0)-1,"")

Hope this helps!

In article ,
bill gras wrote:

I have in column AU cell 39 to cell 500 random sequences of 1-10 rows ,
with numbers in them, (as an result of formulas)
I need to count the number of rows in each sequence that have numbers
of 35 or less , but only up to the first number of 35 or higher. eg:-

AU result
row 39 32 3
40 14
41 19
42 107
43 11
44 21
45 #value!
46 40
47 45
48 16
49 15
50 #value!
51 29 2
52 11
53 146
54 14
55 17
56 11
57 #value!
and so on down to 500 rows

all help is appreciated

thanks
bill gras

Please ignore...

In article ,
Domenic wrote:

Let AU38 contain #VALUE!...

AV39, copied down:

=IF(1-ISNUMBER(AU38),MATCH(TRUE,INDEX(AU39:$AU$50035,0) ,0)-1,"")

Hope this helps!

In article ,
bill gras wrote:

I have in column AU cell 39 to cell 500 random sequences of 1-10 rows ,
with numbers in them, (as an result of formulas)
I need to count the number of rows in each sequence that have numbers
of 35 or less , but only up to the first number of 35 or higher. eg:-

AU result
row 39 32 3
40 14
41 19
42 107
43 11
44 21
45 #value!
46 40
47 45
48 16
49 15
50 #value!
51 29 2
52 11
53 146
54 14
55 17
56 11
57 #value!
and so on down to 500 rows

all help is appreciated

thanks
bill gras

bill gras wrote:
Hi Aladin
No . What I'm saying is that number 3 should be the only number (where it is)
and no number 2 because 107 is greater than 35 and that should be the cut off
point , there fore the next sequence should start after the #value! in
column AU45 . So the next sequence starts at AU 46 where the first number is
greater than 35 and that is the cut off point (no result) . The next sequence
starts at AU 51 after the #value! in AU50 and number 2 is correct , the cut
off point is at AU53 which the number is 146 so no number 3 . The next
sequence starts at AU58 after the #value! of AU57.

I hope this will help , please don't give up
thanks bill

A38:AV57 looks like this, produced with:

=IF(OR(ISERROR(AU39),SUMIF(AU39,"35")35),
"",
IF(IF(ISERROR(AU40),1,AU4035),
COUNTIF(INDEX($AU$39:AU39,
MATCH(9.99999999999999E+307,$AV$38:AV38)):AU39,"<= 35"),
""))

0
32
14
19 3
107
11
21 2
#VALUE!
40
45
16
15 2
#VALUE!
29
11 2
146
14
17
11 3
#VALUE!

What the formula does is to count all values < 35 between the last count
and just before a value 35 or before a #VALUE!. If you want to,
reproduce the desired counts using the quoted section.

Hi Domenic
Thanks for your reply and your time , your fomula works perfect
Once again thanks
regards bill

--
bill gras


"Domenic" wrote:

Let AU38 contain #VALUE!...

AV39, copied down:

=IF(1-ISNUMBER(AU38),MATCH(TRUE,INDEX(AU39:$AU$50035,0) ,0)-1,"")

Hope this helps!

In article ,
bill gras wrote:

I have in column AU cell 39 to cell 500 random sequences of 1-10 rows ,
with numbers in them, (as an result of formulas)
I need to count the number of rows in each sequence that have numbers
of 35 or less , but only up to the first number of 35 or higher. eg:-

AU result
row 39 32 3
40 14
41 19
42 107
43 11
44 21
45 #value!
46 40
47 45
48 16
49 15
50 #value!
51 29 2
52 11
53 146
54 14
55 17
56 11
57 #value!
and so on down to 500 rows

all help is appreciated

thanks
bill gras

Bill,

The formula will return an incorrect result when a sequence does not
contain a number greater than 35. Therefore, try the following formula
instead...

=IF(ISERR(AU38),COUNTIF(AU39:INDEX(AU39:$AU$500,MA TCH(TRUE,IF(ISNUMBER(AU
39:$AU$500),AU39:$AU$50035,ISERR(AU39:$AU$500)),0 )),"<=35"),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
bill gras wrote:

Hi Domenic
Thanks for your reply and your time , your fomula works perfect
Once again thanks
regards bill

--
bill gras

Hi Domenic

I was just about to post to say that I came up with an incorrect result ,
but you beat me to it .
Your new formula works perfect all the way down to 500 rows , no errors !
You are a gentleman and genius.
Thank you very much !

regards bill
--
bill gras


"Domenic" wrote:

Bill,

The formula will return an incorrect result when a sequence does not
contain a number greater than 35. Therefore, try the following formula
instead...

=IF(ISERR(AU38),COUNTIF(AU39:INDEX(AU39:$AU$500,MA TCH(TRUE,IF(ISNUMBER(AU
39:$AU$500),AU39:$AU$50035,ISERR(AU39:$AU$500)),0 )),"<=35"),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
bill gras wrote:

Hi Domenic
Thanks for your reply and your time , your fomula works perfect
Once again thanks
regards bill

--
bill gras