Finding the sum of the two highest consecutive values in a range

msoff

New Member
Joined
Jan 22, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello,


I have a list of weeks with values associated to them as found in the excel picture attached.
I'm trying to find a formula to find the sum of the two highest values from 2 consecutive weeks.
Same thing with 10 consecutive weeks.

Ex:
Row1: Week 1, Week 2, Week 3
Row2: 5, 10, 15
In this example the answer would be 25.

Can you please help me?
 

Attachments

  • Capture.JPG
    Capture.JPG
    18.1 KB · Views: 15

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,639
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Welcome to MrExcelMessage Board.
Try This with CTRL+SHIFT+ENTER if your Data is in A2:K2
Excel Formula:
=MAX(A2:K2+B2:L2)

NewRun.xlsm
ABCDEFGHIJKL
1Week1Week2Week3Week4Week5Week6Week7Week8Week9Week10Week11
2400813812124812
3
424
5
Sheet1
Cell Formulas
RangeFormula
G4G4=MAX(A2:K2+B2:L2)
Press CTRL+SHIFT+ENTER to enter array formulas.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,453
Office Version
  1. 365
Platform
  1. Windows
Allowing for the variable number of consecutive cells, this is the only method that comes to mind. You will need to make a few alterations to the relative references, etc depending on exactly how you want to set up the final sheet.
Book1
ABCDEFGHIJ
1Week 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9Week 10
20012044812120
3
4WeeksResult
5224
6332
7436
81052
912Not enough data
Sheet1
Cell Formulas
RangeFormula
B5:B9B5=IFERROR(MAX(SUBTOTAL(9,OFFSET(A$2,0,COLUMN($A$2:INDEX($A$2:$J$2,1,MATCH(1E+100,$A$2:$J$2)-$A5)),1,$A5))),"Not enough data")
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,753
Maybe:

Book1
ABCDEFGHIJ
1Week 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9Week 10
20012044812120
3
424
Sheet2
Cell Formulas
RangeFormula
A4A4=AGGREGATE(14,6,A2:I2+B2:J2,1)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,589
Messages
5,625,671
Members
416,125
Latest member
NeedExcelHelp2021

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