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?



[TABLE="width: 720"]
<colgroup><col width="64" style="width: 48pt;" span="12"> <col width="192" style="width: 144pt; mso-width-source: userset; mso-width-alt: 7021;"> <tbody>[TR]
[TD="width: 64, bgcolor: #D9D9D9"]Q1 2011[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Q2 2011[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Q3 2011[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Q4 2011[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Q1 2012[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Q2 2012[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Q3 2012[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Q4 2012[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Q1 2013[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Q2 2013[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Q3 2013[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Q4 2013[/TD]
[TD="width: 192, bgcolor: #FFC000"]Average of highest 4 consecutive months[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]34,150[/TD]
[TD="bgcolor: transparent"]41,888[/TD]
[TD="bgcolor: #FFE699"]42,699[/TD]
[TD="bgcolor: #FFE699"]39,514[/TD]
[TD="bgcolor: #FFE699"]55,818[/TD]
[TD="bgcolor: #FFE699"]41,968[/TD]
[TD="bgcolor: transparent"]13,140[/TD]
[TD="bgcolor: transparent"]17,211[/TD]
[TD="bgcolor: transparent"]17,910[/TD]
[TD="bgcolor: transparent"]32,589[/TD]
[TD="bgcolor: transparent"]13,917[/TD]
[TD="bgcolor: transparent"]27,598[/TD]
[TD="bgcolor: transparent"] =AVERAGE(LARGE(A2:L2,4))[/TD]
[/TR]
</tbody>[/TABLE]

 

[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