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

brunohoo

New Member
Joined
May 27, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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):

1590610762354.png


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.

1590610964466.png


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! :)
 

Some videos you may like

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
Joined
Sep 18, 2013
Messages
4,116
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top