How do I count the number of consecutive 5 rows with a certain value?

[xamark_18]

Hi All,

Thanks in advance to anyone who can help me with my problem.

I have a dataset of rainfall from 1951 to present with values ranging from 0 to 100+.
I want to know how many times there is a consecutive 5 days with rainfall value of less than 1.

For example:
Rainfall values= 0, 0, 0, 0, 0, 1.2, 0, 0, 0.2, 0.3, 0, 0.1, 0, 0.9. The answer should be 6.

Thanks,
Christian

[AliGW]

Do you mean the longest run of consecutive days that meet the criterion?

[AliGW]

I believe the answer should be 8:

=MAX(FREQUENCY(IF(A1:A14<1,ROW(A1:A14)),IF(A1:A14>=1,ROW(A1:A14))))

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

This should be updated to encompass the entire range, so change each instance of this: A1:A14

[José Augusto]

Hi

Supposing your data in A2:A_n and using a helper column H with H1=0
In H2 use the following formula and copy down

Please Login or Register  to view this content.

The formula that count the number of consecutive 5 values less than 1 is

Please Login or Register  to view this content.

[José Augusto]

I believe the answer should be 8:
...

I suppose both solutions are wrong and OP is also wrong.
From A1 to A5,count 1
From A7 to A11, 2
From A8 to A12, 3
From A9 to A13, 4
From A10 to A14, 5

[AliGW]

Removed - misread the OP's requirements.

[AliGW]

José - you are right in one respect, however I think it should be two having looked again at the OP's opening post. Let's wait for clarification.

[xamark_18]

Hi AligW and Jose,

Thank you for your answers!

Jose, you're right. The answer should be 5.
I want to know how may consecutive 5 days there is that have rainfall value of below 1.

I would really appreciate it if you can give me the formula for this.

Looking forward to your response.

Regards,
Christian

[José Augusto]

Hi
The solution with a formula, considering the data in A2: A31 can be obtained by formula

Please Login or Register  to view this content.

or using a helper column with this formula

Please Login or Register  to view this content.

you can get the same result with

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Except 1.2 all values are below 1 only. 1 to 5 and 7 to 13 are consecutive days . Count should be 2.

[AliGW]

I agree based on my re-reading of the requirements.

[xamark_18]

Hi kvsrinivasamurthy and Aligw,

I am looking for the number of consecutive 5 rows that is less 1.
From the example the answer should be 5:
From A1 to A5, 1
From A7 to A11, 2
From A8 to A12, 3
From A9 to A13, 4
From A10 to A14, 5

[xamark_18]

Dear Jose,

Thank you for the formula. I'm trying to check if this is correct. I put the formula on may dataset then I tried to delete some of the data to check if the answer will change.
It changed, however instead of decreasing, the answer increased. What do you think is the explanation for this?

Regards,
Christian

[AliGW]

Please provide a workbook showing what you have tried.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[jason.b75]

Jose's formula is counting empty cells as less than 1. This one only counts the actual numbers.

Please Login or Register  to view this content.