Calculating the largest sum of n-consecutive values in a row

[brunohoo]

Hello there.

I have many spreadsheets with precipitation of rainfall. Each one has the height of the rainfall (in millimeters) in a given day of a given month. This is an example of just three months (generally we have 900+ months):

I want to get a formula (if possible) that gets the highest sum possible of 2, 3, 5, 7, 10, 15 and 20 >consecutive< values of rain.

This does not mean reading the highest rain, reading the second highest rain, and then adding them to get Max2days.

What I want is a way of checking every two consecutive days, adding them and giving back the highest sum possible for this month (the same for 2, 3, 5, 7, 10, 15 and 20 days)

Thanks in advance!

 

[StephenCrump]

Welcome to the Forum.

Does your version of Excel 365 have the new dynamic functions? If so:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L
1	N	3
2
3	DataLine
4	1	5	3	1	4	6	-3	4	6	8	-12	2
5
6	Result
7	Maximum	18
8	Place 1	8
Sheet4

Cell Formulas
Range	Formula
B7	B7	=MAX(MMULT(INDEX(DataLine,SEQUENCE(COLUMNS(DataLine)-N+1)+SEQUENCE(,N)-1),SEQUENCE(N,,,0)))
B8	B8	=MATCH(B7,MMULT(INDEX(DataLine,SEQUENCE(COLUMNS(DataLine)-N+1)+SEQUENCE(,N)-1),SEQUENCE(N,,,0)),)

Named Ranges
Name	Refers To	Cells
DataLine	=Sheet4!$A$4:$L$4	B7:B8
N	=Sheet4!$B$1	B7:B8

The cells highlighted illustrate the result for N=3. Maximum is 4+6+8=18, located starting in column #8 in DataLine.

If you want to allow the maximums to wrap across months, e.g. a sequence spanning 30 Jan, 31 Jan and 1 Feb, I'd put all the data into a linear format, i.e. down one column.