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

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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.
 
Upvote 0
Solution

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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
Back
Top