Highest 12-Month Period Average Over Range of Months
 

I need to know if a formula can be constructed that will return the highest
consecutive twelve-month average over a range of months.

Example:
Assume cells A1..A14 represent a single month and contain the following
values.
1, 6, 3, 8, 2, 9, 12, 3, 6, 2, 6, 9, 9, 4

In this example, there are 3 consecutive twelve-month periods, A1..A12,
A2..A13 and A3..A14. The average of A1..A12 = 5.58, A2..A13 = 6.25, and
A3..A14 = 6.08. I want a formula that will return the highest average of the
three, 6.25.

Thanks in advance for any assistance.
-Mark

Highest 12-Month Period Average Over Range of Months
 

Hi Mark

One way
With your data in A1:A14
in B1
=AVERAGE(A1:A12)
copy through B2:B14
In another cell
=MAX(B1:B14)

--
Regards

Roger Govier


"Mark T." <Mark wrote in message
...
I need to know if a formula can be constructed that will return the
highest
consecutive twelve-month average over a range of months.

Example:
Assume cells A1..A14 represent a single month and contain the
following
values.
1, 6, 3, 8, 2, 9, 12, 3, 6, 2, 6, 9, 9, 4

In this example, there are 3 consecutive twelve-month periods,
A1..A12,
A2..A13 and A3..A14. The average of A1..A12 = 5.58, A2..A13 = 6.25,
and
A3..A14 = 6.08. I want a formula that will return the highest average
of the
three, 6.25.

Thanks in advance for any assistance.
-Mark

Highest 12-Month Period Average Over Range of Months
 

Roger,
Thanks for the suggestion. I really need to get it done in one cell with one
formula though (no intermediary formulas below the data). Any suggestions on
that? Is it possible?

Thanks,
-Mark

"Roger Govier" wrote:

Hi Mark

One way
With your data in A1:A14
in B1
=AVERAGE(A1:A12)
copy through B2:B14
In another cell
=MAX(B1:B14)

--
Regards

Roger Govier


"Mark T." <Mark wrote in message
...
I need to know if a formula can be constructed that will return the
highest
consecutive twelve-month average over a range of months.

Example:
Assume cells A1..A14 represent a single month and contain the
following
values.
1, 6, 3, 8, 2, 9, 12, 3, 6, 2, 6, 9, 9, 4

In this example, there are 3 consecutive twelve-month periods,
A1..A12,
A2..A13 and A3..A14. The average of A1..A12 = 5.58, A2..A13 = 6.25,
and
A3..A14 = 6.08. I want a formula that will return the highest average
of the
three, 6.25.

Thanks in advance for any assistance.
-Mark

Highest 12-Month Period Average Over Range of Months
 

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=MAX(SUBTOTAL(1,OFFSET(A1:A14,{1,2,3}-1,,12)))

Biff

"Mark T." <Mark wrote in message
...
I need to know if a formula can be constructed that will return the highest
consecutive twelve-month average over a range of months.

Example:
Assume cells A1..A14 represent a single month and contain the following
values.
1, 6, 3, 8, 2, 9, 12, 3, 6, 2, 6, 9, 9, 4

In this example, there are 3 consecutive twelve-month periods, A1..A12,
A2..A13 and A3..A14. The average of A1..A12 = 5.58, A2..A13 = 6.25, and
A3..A14 = 6.08. I want a formula that will return the highest average of
the
three, 6.25.

Thanks in advance for any assistance.
-Mark

Highest 12-Month Period Average Over Range of Months
 

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):
=MAX(SUBTOTAL(1,OFFSET(A1:A14,{1,2,3}-1,,12)))

Actually, no need to array enter. Enter normally. When I tested I used the
ROW function as the row offset argument which needed array entry.

You could always use this: (but I have a feeling you really want more than
just 3 ranges averaged which is why I posted the Subtotal version!)

=MAX(AVERAGE(A1:A12),AVERAGE(A2:A13),AVERAGE(A3:A1 4))

Biff

"T. Valko" wrote in message
...
Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just ENTER):

=MAX(SUBTOTAL(1,OFFSET(A1:A14,{1,2,3}-1,,12)))

Biff

"Mark T." <Mark wrote in message
...
I need to know if a formula can be constructed that will return the
highest
consecutive twelve-month average over a range of months.

Example:
Assume cells A1..A14 represent a single month and contain the following
values.
1, 6, 3, 8, 2, 9, 12, 3, 6, 2, 6, 9, 9, 4

In this example, there are 3 consecutive twelve-month periods, A1..A12,
A2..A13 and A3..A14. The average of A1..A12 = 5.58, A2..A13 = 6.25, and
A3..A14 = 6.08. I want a formula that will return the highest average of
the
three, 6.25.

Thanks in advance for any assistance.
-Mark

Highest 12-Month Period Average Over Range of Months
 

Hi Biff

Nice solution!
Couldn't get my brain into gear late last night.
--
Regards

Roger Govier


"T. Valko" wrote in message
...
Entered as an array using the key combination of CTRL,SHIFT,ENTER
(not just ENTER):
=MAX(SUBTOTAL(1,OFFSET(A1:A14,{1,2,3}-1,,12)))

Actually, no need to array enter. Enter normally. When I tested I used
the ROW function as the row offset argument which needed array entry.

You could always use this: (but I have a feeling you really want more
than just 3 ranges averaged which is why I posted the Subtotal
version!)

=MAX(AVERAGE(A1:A12),AVERAGE(A2:A13),AVERAGE(A3:A1 4))

Biff

"T. Valko" wrote in message
...
Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER
(not just ENTER):

=MAX(SUBTOTAL(1,OFFSET(A1:A14,{1,2,3}-1,,12)))

Biff

"Mark T." <Mark wrote in message
...
I need to know if a formula can be constructed that will return the
highest
consecutive twelve-month average over a range of months.

Example:
Assume cells A1..A14 represent a single month and contain the
following
values.
1, 6, 3, 8, 2, 9, 12, 3, 6, 2, 6, 9, 9, 4

In this example, there are 3 consecutive twelve-month periods,
A1..A12,
A2..A13 and A3..A14. The average of A1..A12 = 5.58, A2..A13 = 6.25,
and
A3..A14 = 6.08. I want a formula that will return the highest
average of the
three, 6.25.

Thanks in advance for any assistance.
-Mark

Highest 12-Month Period Average Over Range of Months
 

Perfect, that works. Thanks!
-Mark

"T. Valko" wrote:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):
=MAX(SUBTOTAL(1,OFFSET(A1:A14,{1,2,3}-1,,12)))

Actually, no need to array enter. Enter normally. When I tested I used the
ROW function as the row offset argument which needed array entry.

You could always use this: (but I have a feeling you really want more than
just 3 ranges averaged which is why I posted the Subtotal version!)

=MAX(AVERAGE(A1:A12),AVERAGE(A2:A13),AVERAGE(A3:A1 4))

Biff

"T. Valko" wrote in message
...
Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just ENTER):

=MAX(SUBTOTAL(1,OFFSET(A1:A14,{1,2,3}-1,,12)))

Biff

"Mark T." <Mark wrote in message
...
I need to know if a formula can be constructed that will return the
highest
consecutive twelve-month average over a range of months.

Example:
Assume cells A1..A14 represent a single month and contain the following
values.
1, 6, 3, 8, 2, 9, 12, 3, 6, 2, 6, 9, 9, 4

In this example, there are 3 consecutive twelve-month periods, A1..A12,
A2..A13 and A3..A14. The average of A1..A12 = 5.58, A2..A13 = 6.25, and
A3..A14 = 6.08. I want a formula that will return the highest average of
the
three, 6.25.

Thanks in advance for any assistance.
-Mark

Highest 12-Month Period Average Over Range of Months
 

Biff,
Is there a way to have the {1,2,3} part of the formula be variable based on
the count() of number of data items in the range? My data is not static, it
grows each month.

Let me know if you have any thoughts.

Thanks,
-Mark

"T. Valko" wrote:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):
=MAX(SUBTOTAL(1,OFFSET(A1:A14,{1,2,3}-1,,12)))

Actually, no need to array enter. Enter normally. When I tested I used the
ROW function as the row offset argument which needed array entry.

You could always use this: (but I have a feeling you really want more than
just 3 ranges averaged which is why I posted the Subtotal version!)

=MAX(AVERAGE(A1:A12),AVERAGE(A2:A13),AVERAGE(A3:A1 4))

Biff

"T. Valko" wrote in message
...
Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just ENTER):

=MAX(SUBTOTAL(1,OFFSET(A1:A14,{1,2,3}-1,,12)))

Biff

"Mark T." <Mark wrote in message
...
I need to know if a formula can be constructed that will return the
highest
consecutive twelve-month average over a range of months.

Example:
Assume cells A1..A14 represent a single month and contain the following
values.
1, 6, 3, 8, 2, 9, 12, 3, 6, 2, 6, 9, 9, 4

In this example, there are 3 consecutive twelve-month periods, A1..A12,
A2..A13 and A3..A14. The average of A1..A12 = 5.58, A2..A13 = 6.25, and
A3..A14 = 6.08. I want a formula that will return the highest average of
the
three, 6.25.

Thanks in advance for any assistance.
-Mark

Highest 12-Month Period Average Over Range of Months
 

The {1,2,3} means that's how many averages are being calculated. It doesn't
have anything to do with how much data is in the range.

You could make that a variable if you wanted (but it adds complexity to the
formula). Say you wanted 2 averages:

C1 = 2 (number of averages to use in the calc)

Now the formula *is* an *array* and needs to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER):

=MAX(SUBTOTAL(1,OFFSET(A1:A14,ROW(INDIRECT("1:"&C1 ))-1,,12)))

If you add data to the end of the range then you could make the range
dynamic and the references used in the formula will automatically adjust:

http://contextures.com/xlNames01.html#Dynamic

Then (still an array):

=MAX(SUBTOTAL(1,OFFSET(Range,ROW(INDIRECT("1:"&C1) )-1,,12)))

Biff

"Mark T." wrote in message
...
Biff,
Is there a way to have the {1,2,3} part of the formula be variable based
on
the count() of number of data items in the range? My data is not static,
it
grows each month.

Let me know if you have any thoughts.

Thanks,
-Mark

"T. Valko" wrote:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):
=MAX(SUBTOTAL(1,OFFSET(A1:A14,{1,2,3}-1,,12)))

Actually, no need to array enter. Enter normally. When I tested I used
the
ROW function as the row offset argument which needed array entry.

You could always use this: (but I have a feeling you really want more
than
just 3 ranges averaged which is why I posted the Subtotal version!)

=MAX(AVERAGE(A1:A12),AVERAGE(A2:A13),AVERAGE(A3:A1 4))

Biff

"T. Valko" wrote in message
...
Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just ENTER):

=MAX(SUBTOTAL(1,OFFSET(A1:A14,{1,2,3}-1,,12)))

Biff

"Mark T." <Mark wrote in message
...
I need to know if a formula can be constructed that will return the
highest
consecutive twelve-month average over a range of months.

Example:
Assume cells A1..A14 represent a single month and contain the
following
values.
1, 6, 3, 8, 2, 9, 12, 3, 6, 2, 6, 9, 9, 4

In this example, there are 3 consecutive twelve-month periods,
A1..A12,
A2..A13 and A3..A14. The average of A1..A12 = 5.58, A2..A13 = 6.25,
and
A3..A14 = 6.08. I want a formula that will return the highest average
of
the
three, 6.25.

Thanks in advance for any assistance.
-Mark