Highest 12-Month Period Average Over Range of Months

[Mark T.]

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

 

[Roger Govier]

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)

 

[Mark T.]

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

 

[T. Valko]

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

 

[T. Valko]

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:A14))

Biff

 

[Roger Govier]

Hi Biff

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

 

[Mark T.]

Perfect, that works. Thanks!
-Mark

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:A14))

Biff

 

[Mark T.]

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]

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