LARGE formula that finds consecutive numbers

[cothren21]

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?

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

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

 

[Rick Rothstein]

Hello Rick, as always, awesome with formulas (another hero).

Thank you for those kind words, but in actuality, formulas are not my true strength (I kind of got lucky coming up with the one I posted)... I am a programmer through-and-through... that is what I am most comfortable with (I have literally written some kind of code every day since I bought my first computer, a TI99/4, back in 1981).

 

[cothren21]

Rick, Dante, Marcello,
Thank you all for the guidance!

On to the next "fire" to work on...

 

[DanteAmor]

I only helped in awakening the titans