CODEWITHSUNDEEP
excelexcel-formula
E. Gamby
E. Gamby

Reputation: 3

Is there a formula to find the largest value of two adjacent cell?

I have a set of data representing total sales by month from column B to column M. I'd like to create a formula that can find the largest sum of two adjacent cells.

Example

1,3,4,6,6,1,9,2,5,2,1,4

I would want the formula to state 12 (6+6) as the answer, I've only been able to come up with a formula that returns "11" representing the (9+2).

I tried a formula that found the largest value and then used a second formula for finding the larger value of the adjacent cells. I did not take into account that the largest number and it's adjacent cell does not always equate to the largest sum of two adjacent cells.

I used a total of 4 formulas

I used =LARGE(B17:M17,1) in cell R17 and then in Q19 and R19 i used =INDEX(B17:M17,MATCH(R17,B17:M17,0)-1) and =INDEX(B17:M17,MATCH(R17,B17:M17,0)+1) and for the final formula i used =R17+LARGE(Q19:R19,1) –

Upvotes: 0

Views: 724

Answers (2)

Scott Craner
Scott Craner

Reputation: 152450

Use AGGREGATE:

=AGGREGATE(14,6,A1:K1+B1:L1,1)

enter image description here

or LARGE in an Array entry:

=LARGE(A1:K1+B1:L1,1)

Confirm with Ctrl-Shift-Enter instead of Enter when Exiting edit mode

or MAX as an array:

=MAX(A1:K1+B1:L1)

Again, confirm with Ctrl-Shift-Enter instead of Enter when Exiting edit mode

Upvotes: 2

user10862412
user10862412

Reputation:

Use INDEX in its array model and offset one array.

=MAX(INDEX(A1:K1+B1:L1, , ))

enter image description here

Upvotes: 4

Related Questions