Best 3 Consecutive Months
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.
--
If this helps, please click the Yes button.
Cheers,
Shane Devenshire
"Desoto" wrote:
I have 10 years worth of monthly data and need to determine the best (or
worst) three consecutive months for benchmarking purposes. How do I write a
formula that provides me the value of the highest (or lowest) value for three
consecutive months (perhaps array formula?). I want to avoid at all cost
adding another column of data to the worksheet that provides a running three
month average and then utilizing the MAX or MIN function to determine the
value.
Thanks in advance
Rachel
|