Is there a way to utilize the LARGE formula, but have it pick the highest consecutive periods? I am looking at a very wide set of data, 20 years by quarter, and need to find the peak volume for any four-month (consecutive) span. The LARGE function is only finding the biggest four months, although the logic is almost what I need. Here is a small sample of what I a working on, with the current formula I was using (that does not work) written out.
Any ideas on an easy way to do this?
<colgroup><col width="64" style="width: 48pt;" span="12">
<col width="192" style="width: 144pt; mso-width-source: userset; mso-width-alt: 7021;">
<tbody>
</tbody>
Any ideas on an easy way to do this?
Q1 2011 | Q2 2011 | Q3 2011 | Q4 2011 | Q1 2012 | Q2 2012 | Q3 2012 | Q4 2012 | Q1 2013 | Q2 2013 | Q3 2013 | Q4 2013 | Average of highest 4 consecutive months |
34,150 | 41,888 | 42,699 | 39,514 | 55,818 | 41,968 | 13,140 | 17,211 | 17,910 | 32,589 | 13,917 | 27,598 | =AVERAGE(LARGE(A2:L2,4)) |