Excel - Find highest MULTI-cell Total

0
votes

2003-2004 is the largest two-year/cell stretch----how to code this?

Hello!

Is it possible to have Excel find the largest two, three or four cell total within its data? For example, dates are entered as 2000, 2001, 2002, etc in column A and in column B, there's another figure, HRs. I have my document sorted by dates (columnA) and now want to see the most HRs hit over two seasons. This seems very useful and utilized in data but still under-realized.

-Most touchdowns over a two-season stretch. (most touchdowns over a THREE season stretch etc.) -Highest-grossing 3-month stretch. -Rainiest two days ever -Most speeding tickets issued in two days. -Largest two-day Jeopardy winnings. -ETC

Can I search through an excel document and see the largest 2-day rainfall as described? Something similar to "Find All" in excel but for consecutive cells, though, that doesn't find largest I suppose. It'd be cool, if you could drag a range, say 3 cells tall, within a larger range, and Excel could find that largest totals in that larger range.

I doubt this is possible---but surely there is a way data scientists or just newspapers can organize data to find the largest total over a certain stretch? (most HR over a 5-season stretch) How could I do this? Maybe this requires a program for SQL or something? Thank you.

https://www.exceltip.com/summing/sum-the-largest-two-numbers-in-range-in-microsoft-excel.html This seems close, but just finds the two largest figures----not the two largest consecutive figures, which is what I'm looking for.

1
exceldatabasesyntax
Formula or macro solution? – Excel Hero
Yes you could certainly do it by an offset/subtotal combination or possibly offset ranges =max(a2:a12+a3:a13) for small numbers of adjacent cells. – Tom Sharpe

1 Answers

1
votes

Using offset ranges:

=MAX(B2:B12+B3:B13)

or subtotal/offset combo:

=MAX(SUBTOTAL(9,OFFSET(B1,ROW(B1:B11),0,2,1)))

(the first one gets cumbersome when extended to 3,4,5... adjacent cells)

must be entered as array formulas using CtrlShiftEnter

enter image description here

EDIT

If you wanted to find the first, second etc. largest pair you could use Large instead of Max:

=LARGE(B$2:B$12+B$3:B$13,ROW()-1)

or

=LARGE(SUBTOTAL(9,OFFSET(B$1,ROW(B$1:B$11),0,2,1)),ROW()-1)

and then to find the year, use Index/match:

=INDEX(A$2:A$12,MATCH(F2,SUBTOTAL(9,OFFSET(B$1,ROW(B$1:B$11),0,2,1)),0))

The only drawback of this is that if there were two pairs giving the same maximum of 84 say, the index/match would still match the year of the first one. This can be addressed but maybe that is enough for now.