Count number of contiguous values in a range which meet conditions

[LAUGHINGCROW]

I'm wracking my brain trying to write a formula to count the number of values in a range which meet a certain set of conditions.

The data are a list of numbers ranging from 0 - 1. I need to count the number of contiguous data points in each range which exceed a threshold value. This range must also include the maximum value in the data set (which is always the values of "1.0")

Ive attached an example sheet showing the dataset (I have many more datasets to do this on! hence I'm trying to write a formula to automate it). in the example sheet Ive highlighted the cells which contain values which exceed the threshold of "0.5". Looking at dataset number 17 highlights the problem as there are a total of 10 values which exceed the threshold of 0.5, however, the correct result to return is "5" as there are only a maximum of 5 contiguous values which exceed the threshold and for which the range includes the value of 1.0.

Any and all comments/suggestions are gratefully received

[Tony Valko]

Are you sure your results are correct?

If the threshold is N then you want the longest "streak" of consecutive cells that are >N and <=1, correct?

Here are the results I get for the first 3 data sets:

Data Range

	AB	AC	AD
10	6	6	6
11	11	9	9
12	10	3	3

Here are your results for the first 3 data sets:

Data Range

	X	Y	Z
10	6	6	6
11	11	10	9
12	11	4	3

[LAUGHINGCROW]

Apologies, Let me go through the example sheet and check for any errors as a result of my ineptitude. I appreciate the help so far. I'll post back tomorrow.

[ChemistB]

I used in X10 copied down, this Arrayed formula

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

See attachment

[Vikas_Gautam]

That is cool....
ChemistB
Nice Job...

[LAUGHINGCROW]

There were some small errors in the results table I had provided. Apologies, they were present due to human error as a result of manually counting the values..... hence I needed the function!

Many thanks ChemistB your function works perfectly!

[LAUGHINGCROW]

Woops, may have been too hasty! I think there is a problem with the function which is illustrated below:

For dataset #20 with a threshold value of "0.5" the correct result is a value of "4", not "5" as returned by your function. In the example sheet we can see that cells U5:U8 equal or exceed the threshold "0.5" and the range includes the value "1". There is a second range in this dataset which exceeds the threshold value of "0.5" (U15:U19), however, this range does not include the value "1" and so this range is ineligible. The correct range is U5:U8, therefore the correct result is a value of 4.

Do you think you can add in a function to ensure the range includes the value "1"?

[LAUGHINGCROW]

Woops, may have been too hasty! I think there is a problem with the function which is illustrated below:

For dataset #20 (in the example sheet) with a threshold value of "0.5" the correct result is a value of "4", not "5" as returned by your function. In the example sheet we can see that cells U5:U8 equal or exceed the threshold "0.5" and the range includes the value "1". There is a second range in this dataset which exceeds the threshold value of "0.5" (U15:U19), however, this range does not include the value "1" and so this range is ineligible. The correct range is U5:U8, therefore the correct result is a value of 4.

Do you think you can add in a function to ensure the range includes the value "1"?

[ChemistB]

Hmm, struggling with this one. Tony, do you have a solution?

[Tony Valko]

I'm not sure how you'd do that.

[Vikas_Gautam]

Hi ChemistB, Tony and Laughing..
It took me 4 hours at night to get the job done with a Excel formulas.. I got near but wasn't perfect..
So I thought of a UDF that will make the Calculations and lengths better..
I have got the job done... in about 1 hour of hard work.. with UDF..
Moreover it include all kind of possibilities of position of 1...
So here is that..

Please Login or Register  to view this content.

Check the attached file...

[sktneer]

I will request all of you to appreciate, if it is what you were trying to achieve..

You don't need to request to appreciate your good work. The beauty of this forum is, here all the Forum Gurus, Mods and senior members always appreciate good work by any member.

Well done. This is really good work indeed. You have provided OP another option with UDF to achieve the desired result.

[Vikas_Gautam]

Thanks Sktneer..
I have removed that...

Thanks for appreciation...

[kvsrinivasamurthy]

Sorry, duplicate post.

[kvsrinivasamurthy]

Pl see attached file.
One more trail of UDF
Code

Please Login or Register  to view this content.

[Vikas_Gautam]

Thanks for reputation point..

You should also tell which solution you have chosen..

[LAUGHINGCROW]

Vikas,

Thankyou. Your hard work is very much appreciated. The UDF works very well and it will enable me to perform this function much more easily and more accurately than doing the counting manually!

kvsrinivasamurthy,

Your solution also works!

Thanks you both very much

[Vikas_Gautam]

Thank you Tony, ChemistB, LaughingCrow & KVS..
Actually It has been possible because of you all..

Thanks for reputation points as well..
I just can't express my happiness in words...