RAINFALL CESSATION DATE USING INDEX

[Thlama]

Greetings to all on this forum. I am trying to develop a formula that picks rainfall cessation date from series of daily rainfall data. I have done a formula which picks the start of rainfall once a sum of 51mm has been recorded from the start of the year. However, for cessation (offset) date, it should be the last recorded 51(mm) before rainfall recedes for the year. Meaning from the 31st December till date of cessation, the sum of rainfall should not exceed 51(mm).

JIGAWA, NIGERIA RAINFALL DATA.xlsx
	A	B	C	D	E	F	G	H	I
1			Location: Lat 11.7165 Long 9.3348				YEAR	START	CESSATION
2	Month	Day	DOY	1981	1982	1983	1981	28-Apr	10-Apr
3	1-Jan	1	1	0	0	0	1982	4-May
4	2-Jan	2	2	0	0	0	1983	24-May
5	3-Jan	3	3	0	0	0
6	4-Jan	4	4	0	0	0
7	5-Jan	5	5	0	0	0
8	6-Jan	6	6	0	0	0
9	7-Jan	7	7	0	0	0
10	8-Jan	8	8	0	0	0
11	9-Jan	9	9	0	0.03	0
12	10-Jan	10	10	0	0.03	0
13	11-Jan	11	11	0	0.03	0
14	12-Jan	12	12	0	0.03	0
15	13-Jan	13	13	0	0.03	0
16	14-Jan	14	14	0	0.03	0
17	15-Jan	15	15	0	0.03	0
18	16-Jan	16	16	0	0.03	0
19	17-Jan	17	17	0	0.03	0
20	18-Jan	18	18	0	0.03	0
21	19-Jan	19	19	0	0.03	0
22	20-Jan	20	20	0	0.03	0
23	21-Jan	21	21	0	0.03	0
24	22-Jan	22	22	0	0.03	0
25	23-Jan	23	23	0	0.08	0
26	24-Jan	24	24	0	0.09	0
27	25-Jan	25	25	0	0.09	0
28	26-Jan	26	26	0	0.1	0
29	27-Jan	27	27	0	0.1	0
30	28-Jan	28	28	0	0.1	0
31	29-Jan	29	29	0	0.1	0
32	30-Jan	30	30	0	0.1	0
33	31-Jan	31	31	0	0.1	0
34	1-Feb	1	32	0	0.1	0
35	2-Feb	2	33	0	0.1	0
36	3-Feb	3	34	0	0.1	0
37	4-Feb	4	35	0	0.1	0
38	5-Feb	5	36	0	0.1	0
39	6-Feb	6	37	0	0.1	0
40	7-Feb	7	38	0	0.1	0
41	8-Feb	8	39	0	0.1	0
42	9-Feb	9	40	0	0.1	0
43	10-Feb	10	41	0	0.1	0
44	11-Feb	11	42	0	0.1	0
45	12-Feb	12	43	0	0.1	0
46	13-Feb	13	44	0	0.1	0
47	14-Feb	14	45	0	0.1	0
48	15-Feb	15	46	0	0.1	0
49	16-Feb	16	47	0	0.1	0
50	17-Feb	17	48	0	0.1	0
51	18-Feb	18	49	0	0.1	0
52	19-Feb	19	50	0	0.1	0
53	20-Feb	20	51	0	0.1	0
54	21-Feb	21	52	0	0.1	0
55	22-Feb	22	53	0	0.1	0
56	23-Feb	23	54	0	0.1	0
57	24-Feb	24	55	0	0.1	0
58	25-Feb	25	56	0	1.22	0
59	26-Feb	26	57	0	2.03	0
60	27-Feb	27	58	0	2.03	0
61	28-Feb	28	59	0	2.03	0
62	29-Feb	29	60	0	2.03	0
63	1-Mar	1	60	0	2.03	0
64	2-Mar	2	61	0	2.95	0
65	3-Mar	3	62	0	2.97	0
66	4-Mar	4	63	0	2.97	0
67	5-Mar	5	64	0	2.97	0
68	6-Mar	6	65	0	3.57	0
69	7-Mar	7	66	0	3.57	0
70	8-Mar	8	67	0	3.57	0
71	9-Mar	9	68	0	3.57	0
72	10-Mar	10	69	0	3.58	0
73	11-Mar	11	70	0	3.59	0
74	12-Mar	12	71	0.01	3.89	0
75	13-Mar	13	72	0.01	4.52	0
76	14-Mar	14	73	0.59	4.53	0
77	15-Mar	15	74	1.28	8.76	0
78	16-Mar	16	75	1.29	13.54	0
79	17-Mar	17	76	1.29	13.54	0
80	18-Mar	18	77	1.29	13.59	0
81	19-Mar	19	78	1.29	13.61	0
82	20-Mar	20	79	1.29	13.61	0
83	21-Mar	21	80	1.29	13.61	0
84	22-Mar	22	81	1.29	13.61	0
85	23-Mar	23	82	1.29	13.61	0
86	24-Mar	24	83	1.29	13.62	0
87	25-Mar	25	84	1.29	13.63	0
88	26-Mar	26	85	1.29	14.75	0
89	27-Mar	27	86	1.29	14.85	0
90	28-Mar	28	87	1.29	14.85	0
91	29-Mar	29	88	1.29	14.85	0.26
92	30-Mar	30	89	1.29	14.85	0.5
93	31-Mar	31	90	1.29	14.85	1.41
94	1-Apr	1	91	1.29	14.85	2.14
95	2-Apr	2	92	1.29	14.85	2.14
96	3-Apr	3	93	1.29	14.85	2.15
97	4-Apr	4	94	1.33	14.85	2.15
98	5-Apr	5	95	1.36	14.85	2.15
99	6-Apr	6	96	1.37	14.88	2.15
100	7-Apr	7	97	1.44	14.88	2.15
101	8-Apr	8	98	1.45	14.9	2.15
102	9-Apr	9	99	1.45	14.93	2.17
103	10-Apr	10	100	1.45	14.96	2.23
104	11-Apr	11	101	1.45	14.96	2.26
105	12-Apr	12	102	1.45	14.96	2.3
106	13-Apr	13	103	1.45	14.96	2.3
107	14-Apr	14	104	1.45	14.96	2.3
108	15-Apr	15	105	1.45	14.96	2.3
109	16-Apr	16	106	1.45	27.65	2.3
110	17-Apr	17	107	1.46	33.02	2.3
111	18-Apr	18	108	4.2	33.3	2.3
112	19-Apr	19	109	5.04	34.87	2.3
113	20-Apr	20	110	5.7	36.06	2.31
114	21-Apr	21	111	16.2	36.06	2.31
115	22-Apr	22	112	20.02	36.06	2.31
116	23-Apr	23	113	27.49	36.06	2.31
117	24-Apr	24	114	30.62	36.06	2.31
118	25-Apr	25	115	31.81	36.06	2.31
119	26-Apr	26	116	33.82	36.1	2.31
120	27-Apr	27	117	40.08	36.13	2.31
121	28-Apr	28	118	52.2	36.13	2.31
122	29-Apr	29	119	57.4	36.16	2.31
123	30-Apr	30	120	61.54	36.18	2.31
124	1-May	1	121	63.94	38.71	2.31
125	2-May	2	122	66.93	43.72	2.32
126	3-May	3	123	71.92	48.14	2.42
127	4-May	4	124	73.92	51.38	2.42
128	5-May	5	125	75.09	56.48	2.42
129	6-May	6	126	75.13	60.59	2.56
130	7-May	7	127	75.21	62.11	6.45
131	8-May	8	128	75.35	74.42	10.78
132	9-May	9	129	75.61	88.39	13.93
133	10-May	10	130	75.69	88.96	17.04
134	11-May	11	131	82.1	88.96	18.06
135	12-May	12	132	82.89	88.97	19.43
136	13-May	13	133	84.78	88.97	21.85
137	14-May	14	134	87.51	89.06	23.12
138	15-May	15	135	88.49	89.12	26.38
139	16-May	16	136	88.72	90.34	30.82
140	17-May	17	137	88.84	92.16	34.59
141	18-May	18	138	88.88	95.5	36.92
142	19-May	19	139	88.91	100.19	42.12
143	20-May	20	140	89.16	102.43	49.33
144	21-May	21	141	89.73	121.21	49.76
145	22-May	22	142	90.07	129.81	49.78
146	23-May	23	143	90.57	132.66	49.92
147	24-May	24	144	91.33	137.2	51.74
148	25-May	25	145	92.4	139.13	52.45
149	26-May	26	146	94.51	145.67	52.77
150	27-May	27	147	96.08	150.32	52.88
151	28-May	28	148	97.91	155.07	52.92
152	29-May	29	149	98.32	155.88	53.13
153	30-May	30	150	98.5	163.72	53.44
154	31-May	31	151	98.7	165.03	59.84
RESULT SHEET

Cell Formulas
Range	Formula
I2	I2	=INDEX($A$3:$A$368,MATCH(51,$D$21:$D$367,1)+1)
D3:D154	D3	=SUM('RAW DATA'!$D$3:D3)
E3:E154	E3	=SUM('RAW DATA'!$E$3:E3)
F3:F154	F3	=SUM('RAW DATA'!$F$3:F3)
H2	H2	=INDEX($A$3:$A$369,MATCH(51,$D$3:$D$367,1)+1)
H3	H3	=INDEX($A$3:$A$369,MATCH(51,$E$3:$E$367,1)+1)
H4	H4	=INDEX($A$3:$A$369,MATCH(51,$F$3:$F$367,1)+1)
A3:A62	A3	=C3-DATE(YEAR(C3),1,1)+1
A63:A154	A63	=C64-DATE(YEAR(C64),1,1)+1

 

[Thlama]

JIGAWA, NIGERIA RAINFALL DATA.xlsx
	A	B	C	D	E	F
185	1-Jul	1	182	3.27	4.4	6.43
186	2-Jul	2	183	5.09	3.14	3.47
187	3-Jul	3	184	21.9	17.5	2.69
188	4-Jul	4	185	3.06	16	6.64
189	5-Jul	5	186	10.1	1.25	15
190	6-Jul	6	187	9.48	7.54	9.24
191	7-Jul	7	188	6.42	1.51	2.07
192	8-Jul	8	189	11.6	7.34	3.67
193	9-Jul	9	190	2.96	2.69	13.6
194	10-Jul	10	191	12.1	0.16	0.34
195	11-Jul	11	192	6.48	0.46	6.29
196	12-Jul	12	193	10.8	0.11	4.52
197	13-Jul	13	194	6.94	0.62	5.72
198	14-Jul	14	195	9.06	0.85	3.54
199	15-Jul	15	196	1.77	0.37	2.03
200	16-Jul	16	197	7.02	0.53	7.46
201	17-Jul	17	198	18	0.53	5.35
202	18-Jul	18	199	2.34	0.21	5.07
203	19-Jul	19	200	14.7	0.16	2.36
204	20-Jul	20	201	5.06	5.24	14.3
205	21-Jul	21	202	5.47	2.59	8.89
206	22-Jul	22	203	2.56	13.3	16.6
207	23-Jul	23	204	6.99	7.02	7.91
208	24-Jul	24	205	9.38	5.28	8.17
209	25-Jul	25	206	6.71	0.84	3.43
210	26-Jul	26	207	14.7	0.07	8.2
211	27-Jul	27	208	10.5	0.51	17.6
212	28-Jul	28	209	16.4	0.22	12.4
213	29-Jul	29	210	9.53	0.05	40
214	30-Jul	30	211	7.96	5.44	7.29
215	31-Jul	31	212	9.56	1.35	32.4
216	1-Aug	1	213	5.89	2.46	10.9
217	2-Aug	2	214	5.22	2.21	21.9
218	3-Aug	3	215	12.4	1.21	10.9
219	4-Aug	4	216	5.98	3.32	34.4
220	5-Aug	5	217	17.7	3.11	14.7
221	6-Aug	6	218	3.37	3.1	8.67
222	7-Aug	7	219	1.47	6.15	41.5
223	8-Aug	8	220	1.49	2.56	30.3
224	9-Aug	9	221	1.3	37.5	2.84
225	10-Aug	10	222	0.44	23.8	8.72
226	11-Aug	11	223	1.45	7.02	6.42
227	12-Aug	12	224	1.66	2.09	7.11
228	13-Aug	13	225	2.2	7.26	0.73
229	14-Aug	14	226	0.25	2.65	2.21
230	15-Aug	15	227	0.14	2.17	2.2
231	16-Aug	16	228	0.04	0.97	1.21
232	17-Aug	17	229	0.18	8.28	6.01
233	18-Aug	18	230	0.53	11.9	6.33
234	19-Aug	19	231	6.31	11.3	19.8
235	20-Aug	20	232	2.92	21.5	8.4
236	21-Aug	21	233	1.06	21.6	3.83
237	22-Aug	22	234	1.2	7.45	4.9
238	23-Aug	23	235	2.03	22.7	1.56
239	24-Aug	24	236	2.03	1.32	6.75
240	25-Aug	25	237	7.06	0.63	12.1
241	26-Aug	26	238	10.5	0.47	18
242	27-Aug	27	239	17.6	0.08	17.5
243	28-Aug	28	240	20.8	0.65	13.1
244	29-Aug	29	241	6.89	2.63	3.75
245	30-Aug	30	242	5.23	7.3	10.6
246	31-Aug	31	243	8.72	2.13	5.53
247	1-Sep	1	244	0.27	10.2	9.89
248	2-Sep	2	245	5.04	22.1	6.72
249	3-Sep	3	246	3.87	2.83	1.81
250	4-Sep	4	247	10.3	2.77	5.93
251	5-Sep	5	248	19.5	7.2	12.4
252	6-Sep	6	249	8.97	4.43	4.29
253	7-Sep	7	250	2.69	17	7.53
254	8-Sep	8	251	2.16	5.72	15
255	9-Sep	9	252	5.91	9.4	14.3
256	10-Sep	10	253	1.26	5.09	3.71
257	11-Sep	11	254	1.56	4.75	5.51
258	12-Sep	12	255	0.66	28.5	5.9
259	13-Sep	13	256	4.45	4.65	1.4
260	14-Sep	14	257	10.6	8.39	6.99
261	15-Sep	15	258	6.27	3.64	7.99
262	16-Sep	16	259	15.7	2.37	1
263	17-Sep	17	260	5.71	1.02	4.86
264	18-Sep	18	261	0.61	3.11	5.75
265	19-Sep	19	262	0.51	1	3.94
266	20-Sep	20	263	0.25	1.45	2.29
267	21-Sep	21	264	0.25	1.08	7.75
268	22-Sep	22	265	0.59	1.26	7.04
269	23-Sep	23	266	4.54	2.89	1.69
270	24-Sep	24	267	3.22	7.27	1.65
271	25-Sep	25	268	3.75	4.22	2.16
272	26-Sep	26	269	4.21	4.24	3.27
273	27-Sep	27	270	5.23	4.59	2.2
274	28-Sep	28	271	0.71	0.38	0.15
275	29-Sep	29	272	0.77	0.39	0.09
276	30-Sep	30	273	4.41	0.25	0.19
277	1-Oct	1	274	11.6	0.27	0.19
278	2-Oct	2	275	6.37	0.07	0.36
279	3-Oct	3	276	0.14	3.01	0.9
280	4-Oct	4	277	0.02	1.35	5.75
281	5-Oct	5	278	0.03	3.08	0.01
282	6-Oct	6	279	0.02	4	0
283	7-Oct	7	280	0	1.86	0
284	8-Oct	8	281	3.49	0.57	0
285	9-Oct	9	282	1.5	0.59	0
286	10-Oct	10	283	2.72	0.73	0
287	11-Oct	11	284	2.64	0.75	0.01
288	12-Oct	12	285	1.92	0.07	0.07
289	13-Oct	13	286	1.91	0.8	0.04
290	14-Oct	14	287	2.91	4.2	0
291	15-Oct	15	288	9.83	1.18	0
292	16-Oct	16	289	0.48	0.59	0
293	17-Oct	17	290	0	1.23	0
294	18-Oct	18	291	0	0.95	0.02
295	19-Oct	19	292	0	0.99	0.11
296	20-Oct	20	293	0	1.04	0
297	21-Oct	21	294	0.01	0.73	0
298	22-Oct	22	295	0.03	0.45	0
299	23-Oct	23	296	4.32	0.98	0
300	24-Oct	24	297	3.66	1.6	0
301	25-Oct	25	298	1.36	0.93	0
302	26-Oct	26	299	1.57	7.74	0
303	27-Oct	27	300	1.34	1.74	0.01
304	28-Oct	28	301	0.22	0.42	0.05
305	29-Oct	29	302	0.02	0.3	0.08
306	30-Oct	30	303	0.07	0.79	0
307	31-Oct	31	304	0.29	0.03	0
308	1-Nov	1	305	0.07	0.01	0
309	2-Nov	2	306	0.01	0	0
310	3-Nov	3	307	0	0	0
311	4-Nov	4	308	0	0	0
312	5-Nov	5	309	0	0	0
313	6-Nov	6	310	0	0	0.01
314	7-Nov	7	311	0	0	0
315	8-Nov	8	312	0	0	0
316	9-Nov	9	313	0	0	0
317	10-Nov	10	314	0	0	0
318	11-Nov	11	315	0.01	0	0
319	12-Nov	12	316	0	0	0
320	13-Nov	13	317	0	0	0
321	14-Nov	14	318	0	0	0
322	15-Nov	15	319	0.06	0	0.06
323	16-Nov	16	320	0	0	0.02
324	17-Nov	17	321	0	0	0
325	18-Nov	18	322	0	0	0
326	19-Nov	19	323	0	0	0
327	20-Nov	20	324	0	0	0
328	21-Nov	21	325	0	0	0
329	22-Nov	22	326	0	0	0
330	23-Nov	23	327	0	0	0
331	24-Nov	24	328	0	0	0
332	25-Nov	25	329	0	0	0
333	26-Nov	26	330	0	0	0
334	27-Nov	27	331	0	0	0
335	28-Nov	28	332	0	0	0
336	29-Nov	29	333	0	0	0
337	30-Nov	30	334	0	0	0
338	1-Dec	1	335	0	0	0
339	2-Dec	2	336	0	0	0
340	3-Dec	3	337	0	0	0
341	4-Dec	4	338	0	0	0.07
342	5-Dec	5	339	0	0	0
343	6-Dec	6	340	0	0	0
344	7-Dec	7	341	0	0	0
345	8-Dec	8	342	0	0	0
346	9-Dec	9	343	0	0	0
347	10-Dec	10	344	0	0	0
348	11-Dec	11	345	0	0	0
349	12-Dec	12	346	0	0	0
350	13-Dec	13	347	0	0	0
351	14-Dec	14	348	0	0	0.01
352	15-Dec	15	349	0	0	0
353	16-Dec	16	350	0	0	0
354	17-Dec	17	351	0	0	0
355	18-Dec	18	352	0	0	0
356	19-Dec	19	353	0	0	0
357	20-Dec	20	354	0	0	0
358	21-Dec	21	355	0	0	0
359	22-Dec	22	356	0	0	0
360	23-Dec	23	357	0	0	0
361	24-Dec	24	358	0	0	0
362	25-Dec	25	359	0	0	0
363	26-Dec	26	360	0	0	0
364	27-Dec	27	361	0	0	0
365	28-Dec	28	362	0	0	0
366	29-Dec	29	363	0	0	0
367	30-Dec	30	364	0	0	0
368	31-Dec	31	365	0	0	0
369			366
RAW DATA

Cell Formulas
Range	Formula
A185:A368	A185	=C186-DATE(YEAR(C186),1,1)+1