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

[msoff]

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?

 

[maabadi]

Welcome to MrExcelMessage Board.
Try This with CTRL+SHIFT+ENTER if your Data is in A2:K2

=MAX(A2:K2+B2:L2)

NewRun.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Week1	Week2	Week3	Week4	Week5	Week6	Week7	Week8	Week9	Week10	Week11
2	4	0	0	8	13	8	12	12	4	8	12
3
4							24
5
Sheet1

Cell Formulas
Range	Formula
G4	G4	=MAX(A2:K2+B2:L2)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jasonb75]

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
	A	B	C	D	E	F	G	H	I	J
1	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10
2	0	0	12	0	4	4	8	12	12	0
3
4	Weeks	Result
5	2	24
6	3	32
7	4	36
8	10	52
9	12	Not enough data
Sheet1

Cell Formulas
Range	Formula
B5:B9	B5	=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]

Maybe:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10
2	0	0	12	0	4	4	8	12	12	0
3
4	24
Sheet2

Cell Formulas
Range	Formula
A4	A4	=AGGREGATE(14,6,A2:I2+B2:J2,1)