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
|