INDEX and MATCH formula for rainfall cessation

[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 sum of 51(mm) before rainfall recedes for the year. Meaning from the 31st December till date of cessation. The correct formula should pick 2nd Oct as the cessation date since that is the date the rainfall receeds below 51mm. Thanks

DUTSE.xlsx
	F	G	H
215	DOY	Date	Rainfall(mm)
216	214	1-Aug	346.61
217	215	2-Aug	340.72
218	216	3-Aug	335.5
219	217	4-Aug	323.12
220	218	5-Aug	317.14
221	219	6-Aug	299.43
222	220	7-Aug	296.06
223	221	8-Aug	294.59
224	222	9-Aug	293.1
225	223	10-Aug	291.8
226	224	11-Aug	291.36
227	225	12-Aug	289.91
228	226	13-Aug	288.25
229	227	14-Aug	286.05
230	228	15-Aug	285.8
231	229	16-Aug	285.66
232	230	17-Aug	285.62
233	231	18-Aug	285.44
234	232	19-Aug	284.91
235	233	20-Aug	278.6
236	234	21-Aug	275.68
237	235	22-Aug	274.62
238	236	23-Aug	273.42
239	237	24-Aug	271.39
240	238	25-Aug	269.36
241	239	26-Aug	262.3
242	240	27-Aug	251.77
243	241	28-Aug	234.21
244	242	29-Aug	213.4
245	243	30-Aug	206.51
246	244	31-Aug	201.28
247	245	1-Sep	192.56
248	246	2-Sep	192.29
249	247	3-Sep	187.25
250	248	4-Sep	183.38
251	249	5-Sep	173.1
252	250	6-Sep	153.65
253	251	7-Sep	144.68
254	252	8-Sep	141.99
255	253	9-Sep	139.83
256	254	10-Sep	133.92
257	255	11-Sep	132.66
258	256	12-Sep	131.1
259	257	13-Sep	130.44
260	258	14-Sep	125.99
261	259	15-Sep	115.41
262	260	16-Sep	109.14
263	261	17-Sep	93.41
264	262	18-Sep	87.7
265	263	19-Sep	87.09
266	264	20-Sep	86.58
267	265	21-Sep	86.33
268	266	22-Sep	86.08
269	267	23-Sep	85.49
270	268	24-Sep	80.95
271	269	25-Sep	77.73
272	270	26-Sep	73.98
273	271	27-Sep	69.77
274	272	28-Sep	64.54
275	273	29-Sep	63.83
276	274	30-Sep	63.06
277	275	1-Oct	58.65
278	276	2-Oct	47.02
279	277	3-Oct	40.65
280	278	4-Oct	40.51
281	279	5-Oct	40.49
282	280	6-Oct	40.46
283	281	7-Oct	40.44
284	282	8-Oct	40.44
285	283	9-Oct	36.95
286	284	10-Oct	35.45
287	285	11-Oct	32.73
288	286	12-Oct	30.09
289	287	13-Oct	28.17
290	288	14-Oct	26.26
291	289	15-Oct	23.35
292	290	16-Oct	13.52
293	291	17-Oct	13.04
294	292	18-Oct	13.04
295	293	19-Oct	13.04
296	294	20-Oct	13.04
297	295	21-Oct	13.04
298	296	22-Oct	13.03
299	297	23-Oct	13
300	298	24-Oct	8.68
301	299	25-Oct	5.02
302	300	26-Oct	3.66
303	301	27-Oct	2.09
304	302	28-Oct	0.75
305	303	29-Oct	0.53
306	304	30-Oct	0.51
307	305	31-Oct	0.44
308	306	1-Nov	0.15
309	307	2-Nov	0.08
310	308	3-Nov	0.07
311	309	4-Nov	0.07
312	310	5-Nov	0.07
313	311	6-Nov	0.07
314	312	7-Nov	0.07
315	313	8-Nov	0.07
316	314	9-Nov	0.07
317	315	10-Nov	0.07
318	316	11-Nov	0.07
319	317	12-Nov	0.06
320	318	13-Nov	0.06
321	319	14-Nov	0.06
322	320	15-Nov	0.06
323	321	16-Nov	0
324	322	17-Nov	0
325	323	18-Nov	0
326	324	19-Nov	0
327	325	20-Nov	0
328	326	21-Nov	0
329	327	22-Nov	0
330	328	23-Nov	0
331	329	24-Nov	0
332	330	25-Nov	0
333	331	26-Nov	0
334	332	27-Nov	0
335	333	28-Nov	0
336	334	29-Nov	0
337	335	30-Nov	0
338	336	1-Dec	0
339	337	2-Dec	0
340	338	3-Dec	0
341	339	4-Dec	0
342	340	5-Dec	0
343	341	6-Dec	0
344	342	7-Dec	0
345	343	8-Dec	0
346	344	9-Dec	0
347	345	10-Dec	0
348	346	11-Dec	0
349	347	12-Dec	0
350	348	13-Dec	0
351	349	14-Dec	0
352	350	15-Dec	0
353	351	16-Dec	0
354	352	17-Dec	0
355	353	18-Dec	0
356	354	19-Dec	0
357	355	20-Dec	0
358	356	21-Dec	0
359	357	22-Dec	0
360	358	23-Dec	0
361	359	24-Dec	0
362	360	25-Dec	0
363	361	26-Dec	0
364	362	27-Dec	0
365	363	28-Dec	0
366	364	29-Dec	0
367	365	30-Dec	0
START-CESSATION

Cell Formulas
Range	Formula
G216:G367	G216	=F216-DATE(YEAR(F216),1,1)+1
H216:H366	H216	=SUM('Rainfall Data'!$D216:D$367)
H367	H367	=SUM('Rainfall Data'!$D$367:D367)

 

[awoohaw]

try this:

=INDEX(G216:G318,1/(MAX(IFERROR(1/((ROW(H216:H318)-215)/(51>=H216:H318)),0))))

This works when the data starts in row 216, if you move the data you must update the row subtractor (currently 215).

 

[Eric W]

Another option:

=AGGREGATE(14,6,G216:G367/(H215:H366>=51)/(H216:H367<51),1)

 

[awoohaw]

Another option:

=AGGREGATE(14,6,G216:G367/(H215:H366>=51)/(H216:H367<51),1)

@Eric W , I tried using Aggregate, but I was doing it with the ROW numbers, so I was getting value errors. I did not even think to just use the dates as you are. Thanks for showing this. Very insightful.

 

[Thlama]

try this:

=INDEX(G216:G318,1/(MAX(IFERROR(1/((ROW(H216:H318)-215)/(51>=H216:H318)),0))))

This works when the data starts in row 216, if you move the data you must update the row subtractor (currently 215).

Thank you Mr. Moo (laughing). This actually helped helped me realise not to use MATCH on a different data I am working on. Eric's formula delivered the result perfectly.

 

[Thlama]

Another option:

=AGGREGATE(14,6,G216:G367/(H215:H366>=51)/(H216:H367<51),1)

Thanks for the perfect solution. I never thought AGGREGATE alone could resolve this.

 

[Eric W]

Glad we could help!