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

#### brunohoo

##### New Member
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)

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### StephenCrump

##### MrExcel MVP
Welcome to the Forum.

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

Book3
ABCDEFGHIJKL
1N3
2
3DataLine
4153146-3468-122
5
6Result
7Maximum18
8Place 18
Sheet4
Cell Formulas
RangeFormula
B7B7=MAX(MMULT(INDEX(DataLine,SEQUENCE(COLUMNS(DataLine)-N+1)+SEQUENCE(,N)-1),SEQUENCE(N,,,0)))
B8B8=MATCH(B7,MMULT(INDEX(DataLine,SEQUENCE(COLUMNS(DataLine)-N+1)+SEQUENCE(,N)-1),SEQUENCE(N,,,0)),)
Named Ranges
NameRefers ToCells
DataLine=Sheet4!\$A\$4:\$L\$4B7:B8
N=Sheet4!\$B\$1B7: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.

Replies
3
Views
202
Replies
5
Views
435
Replies
0
Views
272
Replies
4
Views
501
Replies
1
Views
191

1,126,953
Messages
5,621,805
Members
415,857
Latest member
braunReivn

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back