Best 3 Consecutive Months
Absolutely PERFECT!!! Thanks Guys
"Glenn" wrote:
Shane Devenshire wrote:
Hi,
Assuming you numbers are in B2:B24 with titles on row 1, enter the following
array formula:
=MAX(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3)))
For the 3 lowest you need to be a little more careful:
=MIN(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3)))
For an array formula you do not type the outer {}'s, you press
SHIFT+CTRL+Enter to enter the formula not Enter.
Of course this doesn't identify the location of those 3 consecutive items.
Array formula for the row number (within the list of numbers) of the start of
those 3 consecutive items:
=MATCH(
MAX(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3))),
SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3)),
0)
.
|