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

Hi,

It may be possible in a single formula but nothing springs to mind so here's
another solution.

Say your data are in column A. Put this in (say) B1 and drag down
=SUM(A1:A3)

You now have a sum of every set of 3 consecutive cell so simply max and min
this new range to get the highest and lowest values for 3 consecutive months.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"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

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

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)

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)
.

Hi Glenn,

Yes I knew that but since the original post didn't ask for it, I just want
to hint that they might want to know something else.

If he wanted we could show him the full address of these results or we could
conditionally format them.

However, it is the core formula that I designed that was the critical
element, I felt.

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

Cheers,
Shane Devenshire


"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)
.

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

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


.

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


.

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


.



.

try generalizing to the max of 30 consecutive numbers.

Yeah, that's true. I would not use or suggest that formula for more than 3
or 4 consecutive cell intervals.

While we're at it...

=MAX(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3)))

If the range to calculate is B2:B24...

ROW(1:21) is not very intuitive in addition to not being very robust.

--
Biff
Microsoft Excel MVP


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


.



.

=MAX(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3)))

I am trying to do this exact same formula, however, I need my array to calculate across columns instead of rows, I have substituted ROW with COLUMN but I get 0.00's. Any advice?



Shane Devenshire wrote:

There is one significant difference, try
01-Feb-10

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:

Previous Posts In This Thread:

On Friday, January 29, 2010 3:03 PM
Desoto wrote:

Best 3 Consecutive Months
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

On Friday, January 29, 2010 3:46 PM
Mike H wrote:

Hi,It may be possible in a single formula but nothing springs to mind so here
Hi,

It may be possible in a single formula but nothing springs to mind so here is
another solution.

Say your data are in column A. Put this in (say) B1 and drag down
=SUM(A1:A3)

You now have a sum of every set of 3 consecutive cell so simply max and min
this new range to get the highest and lowest values for 3 consecutive months.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Desoto" wrote:

On Friday, January 29, 2010 3:49 PM
Shane Devenshire wrote:

Hi,Assuming you numbers are in B2:B24 with titles on row 1, enter the
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 does not identify the location of those 3 consecutive items.

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

Cheers,
Shane Devenshire


"Desoto" wrote:

On Friday, January 29, 2010 4:04 PM
Glenn wrote:

Shane Devenshire wrote:Array formula for the row number (within the list of
Shane Devenshire wrote:

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)

On Friday, January 29, 2010 5:17 PM
Desoto wrote:

Absolutely PERFECT!!! Thanks Guys"Glenn" wrote:
Absolutely PERFECT!!! Thanks Guys

"Glenn" wrote:

On Friday, January 29, 2010 5:43 PM
Shane Devenshire wrote:

Hi Glenn,Yes I knew that but since the original post did not ask for it, I
Hi Glenn,

Yes I knew that but since the original post did not ask for it, I just want
to hint that they might want to know something else.

If he wanted we could show him the full address of these results or we could
conditionally format them.

However, it is the core formula that I designed that was the critical
element, I felt.

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

Cheers,
Shane Devenshire


"Glenn" wrote:

On Friday, January 29, 2010 10:31 PM
T. Valko wrote:

Try these array formulas**
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

On Saturday, January 30, 2010 9:06 AM
Alojz wrote:

Yours is very smart!"T. Valko" wrote:
Yours is very smart!

"T. Valko" wrote:

On Saturday, January 30, 2010 11:10 AM
T. Valko wrote:

Thanks!--BiffMicrosoft Excel MVP
Thanks!

--
Biff
Microsoft Excel MVP

On Monday, February 01, 2010 5:22 PM
Shane Devenshire wrote:

There is one significant difference, try
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:

On Monday, February 01, 2010 6:11 PM
T. Valko wrote:

Yeah, that is true.
Yeah, that is true. I would not use or suggest that formula for more than 3
or 4 consecutive cell intervals.

While we are at it...


If the range to calculate is B2:B24...

ROW(1:21) is not very intuitive in addition to not being very robust.

--
Biff
Microsoft Excel MVP


Submitted via EggHeadCafe - Software Developer Portal of Choice
Get Silverlight 4 Installed: Tips and Tricks
http://www.eggheadcafe.com/tutorials...4-install.aspx

Martin Roland wrote:
=MAX(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3)))

I am trying to do this exact same formula, however, I need my array to calculate across columns instead of rows, I have substituted ROW with COLUMN but I get 0.00's. Any advice?





See if this thread helps:

http://tinyurl.com/2bczrdt

How about a little more detail. What exactly do you want? How do you know
you even want this formula? Tell us what you are trying to calculate, please.

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

Cheers,
Shane Devenshire


"unknown" wrote: