How to count number of consecutive wet days in the a rainfall table?

[yabi100]

I have a rainfall table and the google sheet link is provided below. For each year, I want to count number of days, fulfilling following both two conditions:

1) Number of days where rainfall is more than or equal to 1 mm.

2) Count only those days which are not single.
In other words, I want to count those days where at least two consecutive days are more than 1 mm.
This is called consecutive wet days in hydrology and I have to find a way in Excel to calculate them.
For example, in following part of the table as attached images:
Row 38 and 40 should not be counted because they are single, but rows 42 to 46 (5 days) should be counted, as they are consecutive and more than 1.
I have pasted the table into following link

https://docs.google.com/spreadsheets/d/1pePQbz34OqCFlTI5EwKSFIk2_H7cPNUEzFseDvK9E_w/edit?usp=sharing

I have to do it for each year separately. Separating years will be easy by putting a countif on year. But don't know how to perform consecutive days count.

Is there any way in Excel to perform this calculation?

 

[Jeffrey Mahoney]

I would like to help, For many years I processed all kinds of environmental data including rainfall.

I would create a helper column. Does this get us started?

Book3
	A	B	C	D
1	Date	Rainfall (mm)	Year	Wet Days
2	1/1/2020	7	2020	FALSE
3	1/2/2020	0	2020	FALSE
4	1/3/2020	31	2020	TRUE
5	1/4/2020	8	2020	TRUE
6	1/5/2020	38	2020	TRUE
7	1/6/2020	31	2020	TRUE
8	1/7/2020	27	2020	TRUE
9	1/8/2020	1	2020	TRUE
10	1/9/2020	36	2020	TRUE
11	1/10/2020	25	2020	TRUE
12	1/11/2020	4	2020	TRUE
13	1/12/2020	11	2020	TRUE
14	1/13/2020	27	2020	TRUE
15	1/14/2020	9	2020	TRUE
16	1/15/2020	19	2020	TRUE
17	1/16/2020	3	2020	FALSE
18	1/17/2020	0	2020	FALSE
19	1/18/2020	3	2020	TRUE
Sheet1

Cell Formulas
Range	Formula
C2:C19	C2	=YEAR(A2)
D2:D19	D2	=AND(B2>=1,SUMIFS(B:B,A:A,A2+1,B:B,">="&1))

 

[Fluff]

Is this for Excel or for Google Sheets?
Also the link you supplied is asking people to sign in. You need to share the workbook & then post the link you are given.

 

[Jeffrey Mahoney]

My company won't let me use those links anyway. Best to post as XL2BB

 

[yabi100]

I should add following note as well, to complete the question.
I want the number for longest wet period. Suppose in one year, there are:
6 wet period of 5 days length,
1 wet period of 6 days length and
4 wet period of 10 days length. (all these wet periods are consecutive)
Longest wet period is 10 days.
So the answer for this year will be 10 days.

 

[yabi100]

Is this for Excel or for Google Sheets?
Also the link you supplied is asking people to sign in. You need to share the workbook & then post the link you are given.

Thanks for your reply. Let me download and install XL2BB and upload the file.

 

[yabi100]

DearJefrrey
Thanks for your help. I highly appreciate your help.
Let me add your two helper column and reach you soon.

 

[yabi100]

I did as you showed in above post and got same result. Although I don't understand the formula you input into D3, but results are true and false. How shall I proceed?

 

[Jeffrey Mahoney]

Here is some more

Yabi100.xlsm
	A	B	C	D	E	F	G	H
1	Date	Rainfall (mm)	Year	Wet Days	Count		Percentile	Wet Days
2	1/1/2020	7	2020	FALSE	0		1	260
3	1/2/2020	0	2020	FALSE	0		0.9	102
4	1/3/2020	31	2020	TRUE	13		0.8	57
5	1/4/2020	8	2020	TRUE	12		0.7	42
6	1/5/2020	38	2020	TRUE	11		0.6	32
7	1/6/2020	31	2020	TRUE	10		0.5	24
8	1/7/2020	27	2020	TRUE	9		0.4	17
9	1/8/2020	1	2020	TRUE	8		0.3	11
10	1/9/2020	36	2020	TRUE	7		0.2	6
11	1/10/2020	25	2020	TRUE	6		0.1	2
12	1/11/2020	4	2020	TRUE	5		0	0
13	1/12/2020	11	2020	TRUE	4
14	1/13/2020	27	2020	TRUE	3
15	1/14/2020	9	2020	TRUE	2
16	1/15/2020	19	2020	TRUE	1
17	1/16/2020	3	2020	FALSE	0
18	1/17/2020	0	2020	FALSE	0
19	1/18/2020	3	2020	TRUE	10
20	1/19/2020	38	2020	TRUE	9
Sheet1

Cell Formulas
Range	Formula
C2:C20	C2	=YEAR(A2)
D2:D20	D2	=AND(B2>=1,SUMIFS(B:B,A:A,A2+1,B:B,">="&1))
E2:E20	E2	=IF(D2=TRUE,COUNTA(OFFSET($D2,0,0,MATCH(FALSE,$D2:$D$1000000,0)-1,1)),0)
H2:H12	H2	=PERCENTILE($E$2:$E$1644,G2)

 

[yabi100]

Thanks a lot. You knowledge really helped me.
My assignment in the Hydrology course asks me to do the following steps as I wrote in a separate post above.
I write it here again:
I want the number for longest wet period.
Suppose in one year, there are:
6 wet period of 5 days length,
1 wet period of 6 days length and
4 wet period of 10 days length. (all these wet periods are consecutive)
Longest wet period is 10 days.
So the answer for this year will be 10 days.
Is it possible to calculate this number?

I find out that you calculated wet days as a percentile which is a logical and necessary but my assignment hasn't asked me for it, which is a bit strange.
Will be very happy if you clarify this point, namely following questions:
1- Is your data are limited to one year and there is no need to perform it over several years, or is it acceptable to perform percentile calculation over several years?
2- If I want to find the longest wet period for each year, how can I pursue?
3- I copied your formula to my file but results are strange. Please have a look at it:
Only 100% and 90% are non-zero