There is one significant difference, try generalizing
=MAX(B2:B22+B3:B23+B4:B24)
to the max of 30 consecutive numbers. Then try generalizing
=MAX(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3)))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"T. Valko" wrote:

Thanks!

--
Biff
Microsoft Excel MVP


"Alojz" wrote in message
...
Yours is very smart!

"T. Valko" wrote:

Try these array formulas** :

Max:

=MAX(B2:B22+B3:B23+B4:B24)

Min:

=MIN(B2:B22+B3:B23+B4:B24)

Note the 3 cell offset of the ranges.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
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


.



.